Re: [GENERAL] Matching uppercased russian words (\x0410-\x042F) in UTF8 database 8.4.13
Alexander Farber wrote:
I have prepared an SQL fiddle for my question:
http://sqlfiddle.com/#!11/8a494/4
And also described it in more detail at
http://stackoverflow.com/questions/15500270/string-matching-in-insert-trigger-how-to-use-in-
conditionals-to-return-null
Does anybody please know how to check for
UTF8 range \x0410-\x042F in my code below?
I've tried both
new.word !~ '^[\x0410-\x042F]{2,}$'
(fails with syntax error) and
new.word !~ '^[\u0410-\u042F]{2,}$'
(triggers even for correct words):
Strange, it works here (RHEL 6, x86_64, PostgreSQL 9.2.2,
encoding UTF8, collation and ctype de_DE.UTF8):
test= SELECT 'ПРОВЕРКА' ~ '^[\u0410-\u042F]{2,}$';
?column?
--
t
(1 row)
test= SELECT 'ABCDE' ~ '^[\u0410-\u042F]{2,}$';
?column?
--
f
(1 row)
create table good_words (
word varchar(64) primary key
);
create or replace function keep_clean() returns trigger as $body$
begin
new.word := upper(new.word);
/* next line does not compile? */
IF new.word !~ '^[\x0410-\x042F]{2,}$' THEN
RAISE EXCEPTION 'Not an uppercased Russian word in UTF8';
END IF;
IF new.word ~ '^[ЪЫЬ]' OR new.word ~ 'Ъ$' THEN
return NULL;
END IF;
/* does not return NULL for 'ошибббка'? */
IF new.word ~ '(.)\1\1' AND new.word NOT LIKE '%ШЕЕЕ%'
AND new.word NOT LIKE '%ЗМЕЕЕ%' THEN
return NULL;
This works for me as well:
test= SELECT 'ошибббка' ~ '(.)\1\1'
AND 'ошибббка' NOT LIKE '%ШЕЕЕ%'
AND 'ошибббка' NOT LIKE '%ЗМЕЕЕ%';
?column?
--
t
(1 row)
test= SELECT 'ошиббка' ~ '(.)\1\1'
AND 'ошиббка' NOT LIKE '%ШЕЕЕ%'
AND 'ошиббка' NOT LIKE '%ЗМЕЕЕ%';
?column?
--
f
(1 row)
END IF;
return new;
end;
$body$ language plpgsql;
What do you get for
SELECT pg_encoding_to_char(encoding),
datcollate,
datctype
FROM pg_database WHERE datname = current_database();
and for
SHOW client_encoding;
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Npgsql Integrated Authentication Problem
Hi; I have set up a windows domain trying to run Postgres client using SSPI. I've reached a point that I can use psql to login from my client machine without specifying username and password. But everytime I ran my .NET client program on a domain computer other than the database server itself, I got following error messages. And the program works without error on the database server itself. Any help is much appreciated! Ying Message:FATAL: XX000: could not accept SSPI security context BaseMessage:could not accept SSPI security context Detail:The token supplied to the function is invalid (80090308) Source:Npgsql TargetSite:Boolean MoveNext() My code is very simple: NpgsqlEventLog.Level = LogLevel.Debug; NpgsqlEventLog.LogName = NpgsqlEventLog.txt; NpgsqlConnectionStringBuilder pgsqlCSB = new NpgsqlConnectionStringBuilder(); pgsqlCSB.Database = test; pgsqlCSB.IntegratedSecurity = true; pgsqlCSB.Host = VM-WIN2008; pgsqlCSB.Port = 5432; NpgsqlConnection pgsqlConn = new NpgsqlConnection(pgsqlCSB.ConnectionString); pgsqlConn.Open(); DataTable dt = pgsqlConn.GetSchema(); More log message from NpgsqlEventLog.txt: 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlClosedState.Open() 3/20/2013 10:31:38 AM 1316Debug Attempt to connect to '192.168.73.141'. 3/20/2013 10:31:38 AM 1316Normal Connected to: VM-WIN2008:5432. 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlStartupPacket.NpgsqlStartupPacket() 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlStartupPacket.WriteToStream() 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlStartupPacket.WriteToStream_Ver_3() 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.WriteString() 3/20/2013 10:31:38 AM 1316Debug String written: user. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.WriteString() 3/20/2013 10:31:38 AM 1316Debug String written: ylu. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.WriteString() 3/20/2013 10:31:38 AM 1316Debug String written: database. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.WriteString() 3/20/2013 10:31:38 AM 1316Debug String written: test. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.WriteString() 3/20/2013 10:31:38 AM 1316Debug String written: DateStyle. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.WriteString() 3/20/2013 10:31:38 AM 1316Debug String written: ISO. 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlState.ProcessBackendResponses() 3/20/2013 10:31:38 AM 1316Debug AuthenticationRequest message received from server. 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlStartupState.Authenticate() 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlPasswordPacket.NpgsqlPasswordPacket() 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlPasswordPacket.WriteToStream() 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.WriteBytes() 3/20/2013 10:31:38 AM 1316Debug Unable to find resource string Log_BytesWritten for class PGUtil 3/20/2013 10:31:38 AM 1316Debug AuthenticationRequest message received from server. 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlStartupState.Authenticate() 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlPasswordPacket.NpgsqlPasswordPacket() 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlPasswordPacket.WriteToStream() 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.WriteBytes() 3/20/2013 10:31:38 AM 1316Debug Unable to find resource string Log_BytesWritten for class PGUtil 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.ReadString() 3/20/2013 10:31:38 AM 1316Debug Get NpgsqlEventLog.LogLevel 3/20/2013 10:31:38 AM 1316Debug String read: FATAL. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.ReadString() 3/20/2013 10:31:38 AM 1316Debug Get NpgsqlEventLog.LogLevel 3/20/2013 10:31:38 AM 1316Debug String read: XX000. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.ReadString() 3/20/2013 10:31:38 AM 1316Debug Get NpgsqlEventLog.LogLevel 3/20/2013 10:31:38 AM 1316Debug String read: could not accept SSPI security context. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.ReadString() 3/20/2013 10:31:38 AM 1316Debug Get NpgsqlEventLog.LogLevel 3/20/2013 10:31:38 AM 1316Debug String read: The token supplied to the function is invalid (80090308). 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.ReadString() 3/20/2013 10:31:38 AM 1316Debug Get NpgsqlEventLog.LogLevel 3/20/2013 10:31:38 AM 1316Debug String read: src\backend\libpq\auth.c. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.ReadString() 3/20/2013 10:31:38 AM 1316Debug Get NpgsqlEventLog.LogLevel 3/20/2013 10:31:38 AM 1316Debug String read: 1220. 3/20/2013 10:31:38 AM 1316Debug Entering
[GENERAL] File Fragmentation
Hi, I have a PostgreSQL database with 50 tables. Every minute, sequentially, a batch load 10.000 rows of 250 bytes with a COPY. After a day, i got a database with 50 tables with 1.440 set of 10.000 rows. The tables are cleany and naturally clustered by the inserted timestamp. Each table has data in a file with 1.440 fragments (each day) Now, there is a partition rotation script, that suppress old tables when some size limit happens. Let suppose, that this script runs and suppress only one table qith few days of data, then recreates a new empty one. I got a disk freespace very fragmented, the space used by the rotated table. Then some COPY inserts new data, the tables got new data in theirs files and continue to be fragmented. The new tables begins to grows from the begining of the free space to and is more fragmented that ever. But all the data are always clustered in the tables. After few more rotated tables, all the tables are heavily fragmented and even if the data is clustered inside the file fragments are spread all over the drive. After few days, I see IO wait grows and grows, even when the size of the database stabilises due to the rotation script. I suspect the heavy fragmented files to the cause of the IO wait grows (PostgreSQL on WIndows). How to cope with that ? It seems I can not pregrow file in PostgreSQL. I found a trick: if i created an empty table and i insert dummy data, then i insert good data, then i suppress dummy data, then i vacuum the table (but not a full vacuum) i got a large file with freespace at the begining of the file. If all the files were created with that tricks and larger than the maximum data COPYed, i will have no fragmented files. JG -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Leaking disk space on FreeBSD servers
Hi Guys,
We're seeing a problem with some of our FreeBSD/PostgreSQL servers
leaking quite significant amounts of disk space:
df -h /usr/local/pgsql/
Filesystem SizeUsed Avail Capacity Mounted on
/dev/mfid1s1d1.1T772G222G78%/usr/local/pgsql
du -sh /usr/local/pgsql/
741G/usr/local/pgsql/
Stopping Postgres doesn't fix it, but rebooting does which points at the OS
rather than PG to me. However, the leak is only apparent in the dedicated
pgsql partition, and only on our database servers, so PostgreSQL seems to
at least be involved. The partition itself is a relatively standard UFS
partition:
grep /usr/local/pgsql /etc/fstab
/dev/mfid1s1d /usr/local/pgsqlufs rw 2 2
tunefs -p /usr/local/pgsql/
tunefs: POSIX.1e ACLs: (-a)disabled
tunefs: NFSv4 ACLs: (-N) disabled
tunefs: MAC multilabel: (-l) disabled
tunefs: soft updates: (-n) enabled
tunefs: gjournal: (-J) disabled
tunefs: trim: (-t) disabled
tunefs: maximum blocks per file in a cylinder group: (-e) 2048
tunefs: average file size: (-f)16384
tunefs: average number of files in a directory: (-s) 64
tunefs: minimum percentage of free space: (-m) 8%
tunefs: optimization preference: (-o) time
tunefs: volume label: (-L)
LSOF isn't showing any open files:
lsof +L /usr/local/pgsql/ | awk '{ print $8 }' | grep 0 | wc -l
0
We're not creating filesystem snapshots:
find /usr/local/pgsql/ -flags snapshot
Not all of our servers are leaking space, it's only the more
recently-installed systems. Here's a quick breakdown of versions:
FreeBSD PostgreSQL Leaking?
8.0 8.4.4no
8.2 9.0.4no
8.3 9.1.4yes
8.3 9.2.3yes
9.1 9.2.3yes
Each of these servers is configured with a warm standby, so we've been
switching them over to the standby to reclaim the space (rebooting the
primary is too much downtime). The standby does *not* demonstrate this
problem while it's being used as a standby, but it starts leaking space
once it's been made the primary.
Initially I thought this might be related to WAL files, however the pg_xlog
dir is symlinked outside of the /usr/local/pgsql partition that is
demonstrating this problem:
ll /usr/local/pgsql/data/pg_xlog
lrwxr-xr-x 25B Oct 19 10:48 pg_xlog - /usr/local/pglog/pg_xlog/
I've exhausted everything I can think of to try to solve this one. Has
anyone got any ideas on how to go about debugging this?
Thanks,
Dan
Re: [GENERAL] Leaking disk space on FreeBSD servers
Did you do a detailed du during the supposed problem and after the reboot and
make a diff of those
to fimd any invlolved files/dirs?
That said, i think you might consider posting on freebsd-[questions|stable] as
well.
On Τετ 20 Μαρ 2013 11:49:07 Dan Thomas wrote:
Hi Guys,
We're seeing a problem with some of our FreeBSD/PostgreSQL servers leaking
quite significant amounts of disk space:
df -h /usr/local/pgsql/
Filesystem SizeUsed Avail Capacity Mounted on
/dev/mfid1s1d1.1T772G222G78%/usr/local/pgsql
du -sh /usr/local/pgsql/
741G/usr/local/pgsql/
Stopping Postgres doesn't fix it, but rebooting does which points at the OS
rather than PG to me. However, the leak is only apparent in the dedicated pgsql
partition, and only on our database servers, so PostgreSQL seems to at least be
involved. The partition itself is a relatively standard UFS partition:
grep /usr/local/pgsql /etc/fstab
/dev/mfid1s1d /usr/local/pgsqlufs rw 2 2
tunefs -p /usr/local/pgsql/
tunefs: POSIX.1e ACLs: (-a)disabled
tunefs: NFSv4 ACLs: (-N) disabled
tunefs: MAC multilabel: (-l) disabled
tunefs: soft updates: (-n) enabled
tunefs: gjournal: (-J) disabled
tunefs: trim: (-t) disabled
tunefs: maximum blocks per file in a cylinder group: (-e) 2048
tunefs: average file size: (-f)16384
tunefs: average number of files in a directory: (-s) 64
tunefs: minimum percentage of free space: (-m) 8%
tunefs: optimization preference: (-o) time
tunefs: volume label: (-L)
LSOF isn't showing any open files:
lsof +L /usr/local/pgsql/ | awk '{ print $8 }' | grep 0 | wc -l
0
We're not creating filesystem snapshots:
find /usr/local/pgsql/ -flags snapshot
Not all of our servers are leaking space, it's only the more recently-installed
systems. Here's a quick breakdown of versions:
FreeBSD PostgreSQL Leaking?
8.0 8.4.4no
8.2 9.0.4no
8.3 9.1.4yes
8.3 9.2.3yes
9.1 9.2.3yes
Each of these servers is configured with a warm standby, so we've been
switching them over to the standby to reclaim the space (rebooting the primary
is too much downtime). The standby does *not* demonstrate this problem while
it's being used as a standby, but it starts leaking space once it's been made
the primary.
Initially I thought this might be related to WAL files, however the pg_xlog dir
is symlinked outside of the /usr/local/pgsql partition that is demonstrating
this problem:
ll /usr/local/pgsql/data/pg_xlog
lrwxr-xr-x 25B Oct 19 10:48 pg_xlog - /usr/local/pglog/pg_xlog/
I've exhausted everything I can think of to try to solve this one. Has anyone
got any ideas on how to go about debugging this?
Thanks,
Dan
-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt
Re: [GENERAL] Leaking disk space on FreeBSD servers
Did you do a detailed du during the supposed problem and after the reboot and
make a diff of those to fimd any invlolved files/dirs?
du doesn't show the space in question (du -s shows the actual usage on
disk, df is showing a much higher number), so I doubt this will show
anything up. However, next reboot I'll certainly do that.
That said, i think you might consider posting on freebsd-[questions|stable]
as well.
Yes I think that might be a good plan :)
Dan
On 20 March 2013 12:30, Achilleas Mantzios ach...@matrix.gatewaynet.com wrote:
Did you do a detailed du during the supposed problem and after the reboot
and make a diff of those
to fimd any invlolved files/dirs?
That said, i think you might consider posting on freebsd-[questions|stable]
as well.
On Τετ 20 Μαρ 2013 11:49:07 Dan Thomas wrote:
Hi Guys,
We're seeing a problem with some of our FreeBSD/PostgreSQL servers leaking
quite significant amounts of disk space:
df -h /usr/local/pgsql/
Filesystem SizeUsed Avail Capacity Mounted on
/dev/mfid1s1d1.1T772G222G78%/usr/local/pgsql
du -sh /usr/local/pgsql/
741G/usr/local/pgsql/
Stopping Postgres doesn't fix it, but rebooting does which points at the OS
rather than PG to me. However, the leak is only apparent in the dedicated
pgsql partition, and only on our database servers, so PostgreSQL seems to at
least be involved. The partition itself is a relatively standard UFS
partition:
grep /usr/local/pgsql /etc/fstab
/dev/mfid1s1d /usr/local/pgsqlufs rw 2 2
tunefs -p /usr/local/pgsql/
tunefs: POSIX.1e ACLs: (-a)disabled
tunefs: NFSv4 ACLs: (-N) disabled
tunefs: MAC multilabel: (-l) disabled
tunefs: soft updates: (-n) enabled
tunefs: gjournal: (-J) disabled
tunefs: trim: (-t) disabled
tunefs: maximum blocks per file in a cylinder group: (-e) 2048
tunefs: average file size: (-f)16384
tunefs: average number of files in a directory: (-s) 64
tunefs: minimum percentage of free space: (-m) 8%
tunefs: optimization preference: (-o) time
tunefs: volume label: (-L)
LSOF isn't showing any open files:
lsof +L /usr/local/pgsql/ | awk '{ print $8 }' | grep 0 | wc -l
0
We're not creating filesystem snapshots:
find /usr/local/pgsql/ -flags snapshot
Not all of our servers are leaking space, it's only the more
recently-installed systems. Here's a quick breakdown of versions:
FreeBSD PostgreSQL Leaking?
8.0 8.4.4no
8.2 9.0.4no
8.3 9.1.4yes
8.3 9.2.3yes
9.1 9.2.3yes
Each of these servers is configured with a warm standby, so we've been
switching them over to the standby to reclaim the space (rebooting the
primary is too much downtime). The standby does *not* demonstrate this
problem while it's being used as a standby, but it starts leaking space once
it's been made the primary.
Initially I thought this might be related to WAL files, however the pg_xlog
dir is symlinked outside of the /usr/local/pgsql partition that is
demonstrating this problem:
ll /usr/local/pgsql/data/pg_xlog
lrwxr-xr-x 25B Oct 19 10:48 pg_xlog - /usr/local/pglog/pg_xlog/
I've exhausted everything I can think of to try to solve this one. Has
anyone got any ideas on how to go about debugging this?
Thanks,
Dan
-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Npgsql Integrated Authentication Problem
Hi; I have set up a windows domain trying to run Postgres client using SSPI. I've reached a point that I can use psql to login from my client machine without specifying username and password. But everytime I ran my .NET client program, I got following error messages. Any help is much appreciated! Ying Message:FATAL: XX000: could not accept SSPI security context BaseMessage:could not accept SSPI security context Detail:The token supplied to the function is invalid (80090308) Source:Npgsql TargetSite:Boolean MoveNext() My code is very simple: NpgsqlEventLog.Level = LogLevel.Debug; NpgsqlEventLog.LogName = NpgsqlEventLog.txt; NpgsqlConnectionStringBuilder pgsqlCSB = new NpgsqlConnectionStringBuilder(); pgsqlCSB.Database = test; pgsqlCSB.IntegratedSecurity = true; pgsqlCSB.Host = VM-WIN2008; pgsqlCSB.Port = 5432; NpgsqlConnection pgsqlConn = new NpgsqlConnection(pgsqlCSB.ConnectionString); pgsqlConn.Open(); DataTable dt = pgsqlConn.GetSchema(); More log message from NpgsqlEventLog.txt: 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlClosedState.Open() 3/20/2013 10:31:38 AM 1316Debug Attempt to connect to '192.168.73.141'. 3/20/2013 10:31:38 AM 1316Normal Connected to: VM-WIN2008:5432. 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlStartupPacket.NpgsqlStartupPacket() 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlStartupPacket.WriteToStream() 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlStartupPacket.WriteToStream_Ver_3() 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.WriteString() 3/20/2013 10:31:38 AM 1316Debug String written: user. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.WriteString() 3/20/2013 10:31:38 AM 1316Debug String written: ylu. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.WriteString() 3/20/2013 10:31:38 AM 1316Debug String written: database. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.WriteString() 3/20/2013 10:31:38 AM 1316Debug String written: test. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.WriteString() 3/20/2013 10:31:38 AM 1316Debug String written: DateStyle. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.WriteString() 3/20/2013 10:31:38 AM 1316Debug String written: ISO. 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlState.ProcessBackendResponses() 3/20/2013 10:31:38 AM 1316Debug AuthenticationRequest message received from server. 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlStartupState.Authenticate() 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlPasswordPacket.NpgsqlPasswordPacket() 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlPasswordPacket.WriteToStream() 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.WriteBytes() 3/20/2013 10:31:38 AM 1316Debug Unable to find resource string Log_BytesWritten for class PGUtil 3/20/2013 10:31:38 AM 1316Debug AuthenticationRequest message received from server. 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlStartupState.Authenticate() 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlPasswordPacket.NpgsqlPasswordPacket() 3/20/2013 10:31:38 AM 1316Debug Entering NpgsqlPasswordPacket.WriteToStream() 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.WriteBytes() 3/20/2013 10:31:38 AM 1316Debug Unable to find resource string Log_BytesWritten for class PGUtil 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.ReadString() 3/20/2013 10:31:38 AM 1316Debug Get NpgsqlEventLog.LogLevel 3/20/2013 10:31:38 AM 1316Debug String read: FATAL. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.ReadString() 3/20/2013 10:31:38 AM 1316Debug Get NpgsqlEventLog.LogLevel 3/20/2013 10:31:38 AM 1316Debug String read: XX000. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.ReadString() 3/20/2013 10:31:38 AM 1316Debug Get NpgsqlEventLog.LogLevel 3/20/2013 10:31:38 AM 1316Debug String read: could not accept SSPI security context. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.ReadString() 3/20/2013 10:31:38 AM 1316Debug Get NpgsqlEventLog.LogLevel 3/20/2013 10:31:38 AM 1316Debug String read: The token supplied to the function is invalid (80090308). 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.ReadString() 3/20/2013 10:31:38 AM 1316Debug Get NpgsqlEventLog.LogLevel 3/20/2013 10:31:38 AM 1316Debug String read: src\backend\libpq\auth.c. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.ReadString() 3/20/2013 10:31:38 AM 1316Debug Get NpgsqlEventLog.LogLevel 3/20/2013 10:31:38 AM 1316Debug String read: 1220. 3/20/2013 10:31:38 AM 1316Debug Entering PGUtil.ReadString() 3/20/2013 10:31:38 AM 1316Debug Get NpgsqlEventLog.LogLevel 3/20/2013 10:31:38 AM 1316Debug String read: pg_SSPI_error. 3/20/2013 10:31:38 AM 1316Debug ErrorResponse message
Re: [GENERAL] Leaking disk space on FreeBSD servers
On Τετ 20 Μαρ 2013 12:47:39 Dan Thomas wrote:
Did you do a detailed du during the supposed problem and after the reboot
and make a diff of those to fimd any invlolved files/dirs?
du doesn't show the space in question (du -s shows the actual usage on
disk, df is showing a much higher number), so I doubt this will show
anything up. However, next reboot I'll certainly do that.
du (without -s) shows the whole hierarchy, du -s behaves like du -d 0,
so at this point diff the output of (plain) su is definitely somth worth doing.
That said, i think you might consider posting on freebsd-[questions|stable]
as well.
Yes I think that might be a good plan :)
Dan
On 20 March 2013 12:30, Achilleas Mantzios ach...@matrix.gatewaynet.com
wrote:
Did you do a detailed du during the supposed problem and after the reboot
and make a diff of those
to fimd any invlolved files/dirs?
That said, i think you might consider posting on freebsd-[questions|stable]
as well.
On Τετ 20 Μαρ 2013 11:49:07 Dan Thomas wrote:
Hi Guys,
We're seeing a problem with some of our FreeBSD/PostgreSQL servers leaking
quite significant amounts of disk space:
df -h /usr/local/pgsql/
Filesystem SizeUsed Avail Capacity Mounted on
/dev/mfid1s1d1.1T772G222G78%/usr/local/pgsql
du -sh /usr/local/pgsql/
741G/usr/local/pgsql/
Stopping Postgres doesn't fix it, but rebooting does which points at the OS
rather than PG to me. However, the leak is only apparent in the dedicated
pgsql partition, and only on our database servers, so PostgreSQL seems to at
least be involved. The partition itself is a relatively standard UFS
partition:
grep /usr/local/pgsql /etc/fstab
/dev/mfid1s1d /usr/local/pgsqlufs rw 2 2
tunefs -p /usr/local/pgsql/
tunefs: POSIX.1e ACLs: (-a)disabled
tunefs: NFSv4 ACLs: (-N) disabled
tunefs: MAC multilabel: (-l) disabled
tunefs: soft updates: (-n) enabled
tunefs: gjournal: (-J) disabled
tunefs: trim: (-t) disabled
tunefs: maximum blocks per file in a cylinder group: (-e) 2048
tunefs: average file size: (-f)16384
tunefs: average number of files in a directory: (-s) 64
tunefs: minimum percentage of free space: (-m) 8%
tunefs: optimization preference: (-o) time
tunefs: volume label: (-L)
LSOF isn't showing any open files:
lsof +L /usr/local/pgsql/ | awk '{ print $8 }' | grep 0 | wc -l
0
We're not creating filesystem snapshots:
find /usr/local/pgsql/ -flags snapshot
Not all of our servers are leaking space, it's only the more
recently-installed systems. Here's a quick breakdown of versions:
FreeBSD PostgreSQL Leaking?
8.0 8.4.4no
8.2 9.0.4no
8.3 9.1.4yes
8.3 9.2.3yes
9.1 9.2.3yes
Each of these servers is configured with a warm standby, so we've been
switching them over to the standby to reclaim the space (rebooting the
primary is too much downtime). The standby does *not* demonstrate this
problem while it's being used as a standby, but it starts leaking space once
it's been made the primary.
Initially I thought this might be related to WAL files, however the pg_xlog
dir is symlinked outside of the /usr/local/pgsql partition that is
demonstrating this problem:
ll /usr/local/pgsql/data/pg_xlog
lrwxr-xr-x 25B Oct 19 10:48 pg_xlog - /usr/local/pglog/pg_xlog/
I've exhausted everything I can think of to try to solve this one. Has
anyone got any ideas on how to go about debugging this?
Thanks,
Dan
-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt
-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] File Fragmentation
On Wed, Mar 20, 2013 at 7:13 AM, jg j...@rilk.com wrote: Now, there is a partition rotation script, that suppress old tables when some size limit happens. Let suppose, that this script runs and suppress only one table qith few days of data, then recreates a new empty one. It sounds like you are using partitioned tables. your partitions should be divided up such that they help optimize your queries. that is, minimize the number of partitions you need to scan for any given query. That said, try to make is so that this cleanup script purges whole partitions, not just deleting some rows. That way new data will fill in space without fragmentation.
Re: [GENERAL] File Fragmentation
Hi, It sounds like you are using partitioned tables. your partitions should be divided up such that they help optimize your queries. that is, minimize the number of partitions you need to scan for any given query. That said, try to make is so that this cleanup script purges whole partitions, not just deleting some rows. That way new data will fill in space without fragmentation. The rotated script, as explained, just drops tables and creates empty ones. There are only COPY and SELECT in this database. The problem seems that the IO pattern creates higly fragmented files. I have some files with 1,440 fragments a day. JG -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Leaking disk space on FreeBSD servers
On Wed, Mar 20, 2013 at 7:49 AM, Dan Thomas godd...@gmail.com wrote: Not all of our servers are leaking space, it's only the more recently-installed systems. Here's a quick breakdown of versions: FWIW, I do not observe this behavior. My database has very heavy write load, and old data is purged after it is aged about 7 months, so I do get lots of fragmentation. However, I do not have any disk space phantom loss. How long does it take for you to accumulate this leak? My first instinct is that you have unlinked files still referenced by some application. That is really the only way you get these discrepancies. lsof *should* have showed them to you. Try fstat in case there's some bug in lsof. Also, your tunefs output seems to be not from FreeBSD 9.1. Specifically, it is not emitting this line: tunefs: soft update journaling: (-j) disabled It is a very useful option to turn on for large file systems. I can recover a 6TB file system in about 5 seconds on a crash reboot with that on. [root@d04]# ps axuw34214 USERPID %CPU %MEM VSZRSS TT STAT STARTEDTIME COMMAND pgsql 34214 0.0 0.5 5426964 154484 0- S28Feb13 1:30.66 /usr/local/bin/postgres -D /u/data/postgres [root@d04]# df -h /u/data Filesystem SizeUsed Avail Capacity Mounted on /dev/ufs/ramdisk707G137G513G21%/u/data [root@d04]# du -sh /u/data 137G /u/data [root@d04]# uname -a FreeBSD d04.m1e.net 9.1-RELEASE FreeBSD 9.1-RELEASE #1 r243808: Mon Dec 3 09:56:27 EST 2012 vi...@lorax.kcilink.com:/usr/obj/u/lorax1/usr9/src/sys/KCI64 amd64 [root@d04]# uptime 9:50AM up 74 days, 17:36, 1 user, load averages: 0.21, 0.18, 0.17 [root@d04]# psql --version psql (PostgreSQL) 9.2.3 [root@d04]#
Re: [GENERAL] File Fragmentation
On Wed, Mar 20, 2013 at 9:53 AM, jg j...@rilk.com wrote: The rotated script, as explained, just drops tables and creates empty ones. That doesn't make sense then, to have fragmentation if you are creating new tables with fresh data copied into them. The files should be pretty much sequentially written. O I see. You're using Windows. Maybe you need some OS with a better file system that doesn't fragment like that?
Re: [GENERAL] Matching uppercased russian words (\x0410-\x042F) in UTF8 database 8.4.13
Alexander Farber wrote: It seems to be better in 9.2.x? Yes, as Tom has pointed out. I didn't see that you were on 8.4 when I wrote my answer. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Matching uppercased russian words (\x0410-\x042F) in UTF8 database 8.4.13
Alexander Farber alexander.far...@gmail.com writes: Thanks for trying! I am using CentOS 6.3 It seems to be better in 9.2.x? As stated upthread, 8.4 doesn't understand \u escapes. You'd need to put in the characters another way --- either literally, or using octal escapes to spell out the UTF8 encoding. I think it will work in 8.4 if you do, but not 100% sure. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] File Fragmentation
On 03/20/2013 07:14 AM, Vick Khera wrote: On Wed, Mar 20, 2013 at 9:53 AM, jg j...@rilk.com mailto:j...@rilk.com wrote: The rotated script, as explained, just drops tables and creates empty ones. That doesn't make sense then, to have fragmentation if you are creating new tables with fresh data copied into them. The files should be pretty much sequentially written. I think the problem is here: Every minute, sequentially, a batch load 10.000 rows of 250 bytes with a COPY. After a day, i got a database with 50 tables with 1.440 set of 10.000 rows. So if I am reading it right the table starts with 10,000 rows then 10,000 rows are added each minute during the day. O I see. You're using Windows. Maybe you need some OS with a better file system that doesn't fragment like that? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] File Fragmentation
Hi, That doesn't make sense then, to have fragmentation if you are creating new tables with fresh data copied into them. The files should be pretty much sequentially written. O I see. You're using Windows. Maybe you need some OS with a better file system that doesn't fragment like that? I know OS other than Windows will be better. But, I think on any OS, I would have some trouble because the pattern of IO. Each minute, each of the 50 tables has 10,000 new rows COPYed. Normaly the filesystem try to keep the file (under the tables) continous with few fragment. But the pattern is such, that it seems to me really difficult to prevent it. Do you have any idea to mitigate the problem on Windows ? JG -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] File Fragmentation
jg, 20.03.2013 12:13: I suspect the heavy fragmented files to the cause of the IO wait grows (PostgreSQL on WIndows). How to cope with that? I would first investigate that it's *really* the fragmentation. As a database does a lot of random IO, fragmentation isn't such a big issue. You could use e.g. contig[1] from SysInternals to de-fragment the data files and then check if that really improves performance. Thomas [1] http://technet.microsoft.com/de-de/sysinternals/bb897428 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Leaking disk space on FreeBSD servers
regarding journaling, there is the counter argument that you do not need to do the same job twice, in the sense that we already spend a considerable amount of time retaining the WAL in postgresql, no need to redo the same on FS level. Crush-intensive systems (for lack of a better word) might benefit from FS journaling, but the best option here is try and find the cause. FreeBSD systems are supposed to not crush, that's why ppl use them in the first place. On Τετ 20 Μαρ 2013 10:11:58 Vick Khera wrote: On Wed, Mar 20, 2013 at 7:49 AM, Dan Thomas godd...@gmail.com wrote: Not all of our servers are leaking space, it's only the more recently-installed systems. Here's a quick breakdown of versions: FWIW, I do not observe this behavior. My database has very heavy write load, and old data is purged after it is aged about 7 months, so I do get lots of fragmentation. However, I do not have any disk space phantom loss. How long does it take for you to accumulate this leak? My first instinct is that you have unlinked files still referenced by some application. That is really the only way you get these discrepancies. lsof *should* have showed them to you. Try fstat in case there's some bug in lsof. Also, your tunefs output seems to be not from FreeBSD 9.1. Specifically, it is not emitting this line: tunefs: soft update journaling: (-j) disabled It is a very useful option to turn on for large file systems. I can recover a 6TB file system in about 5 seconds on a crash reboot with that on. [root@d04]# ps axuw34214 USERPID %CPU %MEM VSZRSS TT STAT STARTEDTIME COMMAND pgsql 34214 0.0 0.5 5426964 154484 0- S28Feb13 1:30.66 /usr/local/bin/postgres -D /u/data/postgres [root@d04]# df -h /u/data Filesystem SizeUsed Avail Capacity Mounted on /dev/ufs/ramdisk707G137G513G21%/u/data [root@d04]# du -sh /u/data 137G/u/data [root@d04]# uname -a FreeBSD d04.m1e.net 9.1-RELEASE FreeBSD 9.1-RELEASE #1 r243808: Mon Dec 3 09:56:27 EST 2012 vi...@lorax.kcilink.com:/usr/obj/u/lorax1/usr9/src/sys/KCI64 amd64 [root@d04]# uptime 9:50AM up 74 days, 17:36, 1 user, load averages: 0.21, 0.18, 0.17 [root@d04]# psql --version psql (PostgreSQL) 9.2.3 [root@d04]# - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] Leaking disk space on FreeBSD servers
On Wed, Mar 20, 2013 at 10:34 AM, Achilleas Mantzios ach...@matrix.gatewaynet.com wrote: regarding journaling, there is the counter argument that you do not need to do the same job twice, in the sense that we already spend a considerable amount of time retaining the WAL in postgresql, no need to redo the same on FS level. There's a difference in the file system integrity and the DB integrity. PG will keep the DB integrity just fine without the file system journaling. The journaling just makes recovery from crash that much faster. ie, running fsck on 6TB of disk storage takes a LONG time, sometimes hours, but with the journal enabled, it takes a few seconds.
Re: [GENERAL] File Fragmentation
Hi, I create a test cas on Linux: postgres=# create table a (v int); postgres=# create table b (v int); Then a while(true) over the following script where 24577 and 24580 are the files of the tables a and b #!/bin/sh psql test -c 'insert into a select generate_series(1,10,1);' psql test -c 'insert into b select generate_series(1,10,1);' psql test -c 'checkpoint;' /usr/sbin/filefrag -v 24577 24580 ls -lh 24577 24580 After few minutes, I got 100 extend by files. The file fragmentation happens on Windows and Linux, too. I not sure that the Wait IO on Windows is related to file fragmentation. I try to find a way to analyse the situation. JG -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Leaking disk space on FreeBSD servers
Of course, but does it make sense for you to pay the ~ 5%/day performance penalty for the ~0.5%/year chance of having your system crush? Unless your FreeBSD server is stuffed with exotic gamer hardware, i don't see the likehood of crush getting larger than that. On Τετ 20 Μαρ 2013 10:39:58 Vick Khera wrote: On Wed, Mar 20, 2013 at 10:34 AM, Achilleas Mantzios ach...@matrix.gatewaynet.com wrote: regarding journaling, there is the counter argument that you do not need to do the same job twice, in the sense that we already spend a considerable amount of time retaining the WAL in postgresql, no need to redo the same on FS level. There's a difference in the file system integrity and the DB integrity. PG will keep the DB integrity just fine without the file system journaling. The journaling just makes recovery from crash that much faster. ie, running fsck on 6TB of disk storage takes a LONG time, sometimes hours, but with the journal enabled, it takes a few seconds. - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] Leaking disk space on FreeBSD servers
How long does it take for you to accumulate this leak? It grows at between 2 and 4 gigabytes per day on average. It seems to be related to load on the database, as it grows slower over the weekends when the servers are under less load. Here's a graph that shows growth of one server (from reboot to about ~30gb difference) over the last couple of weeks: http://i.imgur.com/AwzQ46j.png The flatter parts are the weekends, but otherwise it's reasonably constant. That is really the only way you get these discrepancies. lsof *should* have showed them to you. Try fstat in case here's some bug in lsof. After a bit of messing around with fstat and find (it doesn't make it easy!) I've confirmed all the inodes fstat is reporting exist on disk, which backs up lsof. I also confirmed both lsof and fstat were detecting unlinked files by manually holding a file open and unlinking it. Can't see much evidence that it's an open file. Also, your tunefs output seems to be not from FreeBSD 9.1 That example was from an 8.3 machine (it's the one I'm testing with as it's got the biggest disk usage deficit). The 9.1 box has this: tunefs: soft update journaling: (-j) enabled ..but is still exhibiting this behaviour. FWIW, I do not observe this behaviour We actually have another FreeBSD8.3/PG9.1 machine under different (but similar) load that *doesn't* demonstrate this behaviour. There's nothing obvious in the differences in usage patterns that we can see (we're not using any exotic features or anything), but it certainly suggests that it's *something* related to PG or our usage of it. On 20 March 2013 14:11, Vick Khera vi...@khera.org wrote: On Wed, Mar 20, 2013 at 7:49 AM, Dan Thomas godd...@gmail.com wrote: Not all of our servers are leaking space, it's only the more recently-installed systems. Here's a quick breakdown of versions: FWIW, I do not observe this behavior. My database has very heavy write load, and old data is purged after it is aged about 7 months, so I do get lots of fragmentation. However, I do not have any disk space phantom loss. How long does it take for you to accumulate this leak? My first instinct is that you have unlinked files still referenced by some application. That is really the only way you get these discrepancies. lsof *should* have showed them to you. Try fstat in case there's some bug in lsof. Also, your tunefs output seems to be not from FreeBSD 9.1. Specifically, it is not emitting this line: tunefs: soft update journaling: (-j) disabled It is a very useful option to turn on for large file systems. I can recover a 6TB file system in about 5 seconds on a crash reboot with that on. [root@d04]# ps axuw34214 USERPID %CPU %MEM VSZRSS TT STAT STARTEDTIME COMMAND pgsql 34214 0.0 0.5 5426964 154484 0- S28Feb13 1:30.66 /usr/local/bin/postgres -D /u/data/postgres [root@d04]# df -h /u/data Filesystem SizeUsed Avail Capacity Mounted on /dev/ufs/ramdisk707G137G513G21%/u/data [root@d04]# du -sh /u/data 137G /u/data [root@d04]# uname -a FreeBSD d04.m1e.net 9.1-RELEASE FreeBSD 9.1-RELEASE #1 r243808: Mon Dec 3 09:56:27 EST 2012 vi...@lorax.kcilink.com:/usr/obj/u/lorax1/usr9/src/sys/KCI64 amd64 [root@d04]# uptime 9:50AM up 74 days, 17:36, 1 user, load averages: 0.21, 0.18, 0.17 [root@d04]# psql --version psql (PostgreSQL) 9.2.3 [root@d04]# -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Leaking disk space on FreeBSD servers
On Ôåô 20 Ìáñ 2013 15:15:23 Dan Thomas wrote: We actually have another FreeBSD8.3/PG9.1 machine under different (but similar) load that *doesn't* demonstrate this behaviour. There's nothing obvious in the differences in usage patterns that we can see (we're not using any exotic features or anything), but it certainly suggests that it's *something* related to PG or our usage of it. Any difference in the architecture of the two systems? (x86, amd64, etc..) Any difference in the respective output of % pg_config ? - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] File Fragmentation
Hi, Atfer 30 minutes, on my Linux computer, with 2 files fill one after the other. I got a fragmented files with many back step: # /usr/sbin/filefrag -v 24586 Filesystem type is: ef53 File size of 24586 is 822231040 (200740 blocks, blocksize 4096) ext logical physical expected length flags 0 0 22630402048 12048 2271232 2265087 2048 24096 2277376 2273279 2048 36144 2289664 2279423 2048 48192 2306048 2291711658 58850 2306707 2306705 1390 6 10240 2316288 2308096 2048 7 12288 2308097 2318335102 8 12390 2328576 2308198 1946 9 14336 2336768 2330521 2048 10 16384 2347008 2338815 4096 11 20480 2357248 2351103 2048 12 22528 2385920 2359295 4096 13 26624 2416640 2390015 2048 14 28672 2424832 2418687 4096 15 32768 2439168 2428927 2048 16 34816 2582528 2441215 2048 17 36864 2940928 2584575 2048 18 38912 3045376 2942975 2048 19 40960 1845248 3047423 2048 20 43008 1910784 1847295 2048 21 45056 2017280 1912831 2048 22 47104 2029568 2019327 2048 23 49152 2146304 2031615 2048 24 51200 2213888 2148351 2048 25 53248 3096576 2215935 2048 26 5529640960 3098623 2048 27 573449011243007 2048 28 59392 12492892159 2048 29 61440 102400 126975 2048 30 63488 161792 104447 2048 31 65536 164609 163839680 32 66216 243712 165288 1368 33 67584 307200 245079 2048 34 69632 372736 309247 2048 35 71680 448512 374783 2048 36 73728 495616 450559 2048 37 75776 577536 497663 2048 38 77824 649216 579583 2048 39 79872 724992 651263 2048 40 81920 757760 727039 2048 41 83968 849920 759807 2048 42 86016 909312 851967 2048 43 88064 929792 911359 2048 44 90112 972800 931839 2048 45 92160 968704 974847604 46 92764 1040384 969307 1444 47 94208 1081344 1041827 2048 48 96256 1134592 1083391 2048 49 98304 1171456 1136639 2048 50 100352 1165312 1173503 2048 51 102400 1202176 1167359 2048 52 104448 1234944 1204223 2048 53 106496 1267712 1236991 2048 54 108544 1298432 1269759 2048 55 110592 1325056 1300479 2048 56 112640 1372160 1327103 2048 57 114688 1384448 1374207 2048 58 116736 1433600 1386495 2048 59 118784 1452032 1435647 2048 60 120832 1499136 1454079 2048 61 122880 1529856 1501183 2048 62 124928 1560576 1531903 2048 63 126976 1687552 1562623 2048 64 129024 2125824 1689599 2048 65 131072 534560 2127871 2048 66 133120 544800 536607 2048 67 135168 1056800 546847 2048 68 137216 2629789 1058847 6144 69 143360 2867200 2635932 2048 70 145408 2887680 2869247 2048 71 14745675776 2889727 2048 72 149504 299008077823 2048 73 151552 3014656 2992127 2048 74 153600 3094528 3016703 2048 75 155648 3117056 3096575 2048 76 15769663488 3119103 2048 77 159744 19046465535 2048 78 161792 215040 192511 2048 79 163840 284672 217087 2048 80 165888 378880 286719 2048 81 167936 419840 380927 2048 82 169984 432128 421887 2048 83 172032 501760 434175 2048 84 174080 598016 503807 2048 85 176128 659456 600063 2048 86 178176 700416 661503 2048 87 180224 772096 702463 2048 88 182272 829440 774143 2048 89 184320 864256 831487 2048 90 186368 903168 866303 2048 91 188416 1030144 905215 2048 92 190464 1255424 1032191 2048 93 192512 1431552 1257471 2048 94 194560 1542144 1433599 2048 95 196608 1732608 1544191 2048 96 198656 1740800 1734655 2048 97 200704 1787904 1742847 36 eof 24586: 98 extents found You can see 3 back steps: position 26, 71, 76. Just imagine, 50 files over few days, the files become heavely fragmented with many back steps. So a sequential scan (logicaly from PostgreSQL point of view) is in fact random (filesystem point of view). JG Le Mercredi 20 Mars 2013 15:47 CET, j...@rilk.com a écrit: Hi, I create a test cas on Linux: postgres=# create table a (v int); postgres=# create table b (v int); Then a while(true) over the following script where 24577 and 24580 are the files of the tables a and b #!/bin/sh psql test -c 'insert into a select generate_series(1,10,1);' psql test -c 'insert into b select generate_series(1,10,1);' psql test -c 'checkpoint;' /usr/sbin/filefrag -v 24577 24580 ls -lh 24577 24580 After few minutes, I got 100 extend by files. The file fragmentation happens on Windows and Linux, too. I not sure that the Wait IO on Windows is related to file fragmentation.
Re: [GENERAL] Leaking disk space on FreeBSD servers
Any difference in the architecture of the two systems? (x86, amd64, etc..) Any difference in the respective output of % pg_config Alas, no. Both identical machines running identical versions of FreeBSD and PG. pg_config on the two machines matches exactly. On 20 March 2013 15:37, Achilleas Mantzios ach...@matrix.gatewaynet.com wrote: On Ôåô 20 Ìáñ 2013 15:15:23 Dan Thomas wrote: We actually have another FreeBSD8.3/PG9.1 machine under different (but similar) load that *doesn't* demonstrate this behaviour. There's nothing obvious in the differences in usage patterns that we can see (we're not using any exotic features or anything), but it certainly suggests that it's *something* related to PG or our usage of it. Any difference in the architecture of the two systems? (x86, amd64, etc..) Any difference in the respective output of % pg_config ? - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Leaking disk space on FreeBSD servers
Dan Thomas godd...@gmail.com wrote: We're seeing a problem with some of our FreeBSD/PostgreSQL servers leaking quite significant amounts of disk space: Stopping Postgres doesn't fix it, but rebooting does which points at the OS rather than PG to me. However, the leak is only apparent in the dedicated pgsql partition, and only on our database servers, so PostgreSQL seems to at least be involved. The partition itself is a relatively standard UFS partition: I saw something once which *might* be related. I don't recall the OS of FS involved, but in an attempt to reduce the fragmentation of files which started small and eventually grew large, a large allocation of contiguous space was made on file creation, and that space was not release as long as any page for the file remained in the OS cache. In the instance where I saw the problem, autovacuum had been turned off and the instance was just coming up on the point where wraparound prevention runs were about to be triggered. pg_clog was where most of the wasted space was. No guarantees that this is the issue, but it sounded similar -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Leaking disk space on FreeBSD servers
On 03/20/2013 01:25 PM, Kevin Grittner wrote: I saw something once which *might* be related. I don't recall the OS of FS involved, but in an attempt to reduce the fragmentation of files which started small and eventually grew large, a large allocation of contiguous space was made on file creation, and that space was not release as long as any page for the file remained in the OS cache. That was an optimization decision made for XFS in recent kernels, and the chunks it grabs are very, very large. We had to reduce the default allocation size to 1MB to disable the elastic allocation system. In the end, we regained about 50GB of phantom space after a re-mount, and it's stayed that way since. But that's what du --apparent-size is for. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can't terminate hung COPY
PostgreSQL 8.4.16 on CentOS 5.9. I've run into a situation where executing a \COPY from psql will hang and at that point it's impossible to terminate the COPY command. I've tried pg_cancel_backend and pg_terminalte_backend - even sending the process itself a TERM signal. Sending the process KILL works, but of course that restarts the entire server which is far from ideal. Any idea on how to cleanly terminal the offending process at this point? Any idea on what may cause \COPY to hang and how to prevent it? Thanks Dave -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] State of the art re: group default privileges
I'm running into this exact situation: http://www.postgresql.org/message-id/CAG1_KcBFM0e2buUG=o7ojq_ktadrzdgd45ju7gke3duz0sz...@mail.gmail.com We really need to be able to have a group of developers who can create things and modify each others' stuff[1]. Is it still more or less impossible? The workaround that comes to mind is a script to enumerate all developers and then set the defaults one at a time. This breaks however when we add a new developer -- he can't access any of the existing stuff. [1] WITHOUT making them set their own permissions. For the sake of the discussion, let's say they can't be trusted to get it right, or more likely, don't feel like it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] State of the art re: group default privileges
Michael Orlitzky wrote: I'm running into this exact situation: http://www.postgresql.org/message-id/CAG1_KcBFM0e2buUG=o7ojq_ktadrzdgd45ju7gke3duz0sz...@mail.gmail.com We really need to be able to have a group of developers who can create things and modify each others' stuff[1]. Is it still more or less impossible? The workaround that comes to mind is a script to enumerate all developers and then set the defaults one at a time. This breaks however when we add a new developer -- he can't access any of the existing stuff. I don't understand. Why doesn't alice do a set role dev_user before creating the table? Then, the table owner is dev_user, not alice, and default privileges for dev_user apply. In fact you needn't run ALTER DEFAULT PRIVILEGES at all, because dev_user will be owner of the objects, and both alice and bob have that role. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can't terminate hung COPY
2013/3/20 David Rees dree...@gmail.com: PostgreSQL 8.4.16 on CentOS 5.9. I've run into a situation where executing a \COPY from psql will hang and at that point it's impossible to terminate the COPY command. I've tried pg_cancel_backend and pg_terminalte_backend - even sending the process itself a TERM signal. Sending the process KILL works, but of course that restarts the entire server which is far from ideal. Any idea on how to cleanly terminal the offending process at this point? Any idea on what may cause \COPY to hang and how to prevent it? What happens if you use COPY ... FROM with the same data? Are you sure the process hangs (strange thing is that you can't terminate the backend)? Could it be that it looks like it hung, but it's actually COPYing a huge chunk of data? -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] State of the art re: group default privileges
On 03/20/2013 04:12 PM, Alvaro Herrera wrote: Michael Orlitzky wrote: I'm running into this exact situation: http://www.postgresql.org/message-id/CAG1_KcBFM0e2buUG=o7ojq_ktadrzdgd45ju7gke3duz0sz...@mail.gmail.com We really need to be able to have a group of developers who can create things and modify each others' stuff[1]. Is it still more or less impossible? The workaround that comes to mind is a script to enumerate all developers and then set the defaults one at a time. This breaks however when we add a new developer -- he can't access any of the existing stuff. I don't understand. Why doesn't alice do a set role dev_user before creating the table? Then, the table owner is dev_user, not alice, and default privileges for dev_user apply. In fact you needn't run ALTER DEFAULT PRIVILEGES at all, because dev_user will be owner of the objects, and both alice and bob have that role. It comes down to a separation of concerns. These developers shouldn't (and really, don't) know/care what the privileges should be. They don't know that they're even in a group. Why should they? As with filesystem permissions, the admin should be able to set this all up (correctly) and forget about it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fast Shutdown (SIGINT) results in a CHECKPOINT...
For a while now I've known that sending a SIGINT can trigger a CHECKPOINT, but I don't know if this is intentional or a bug. Logs from today: 2013-03-20_18:21:43.27642 LOG: received fast shutdown request 2013-03-20_18:21:43.27645 LOG: aborting any active transactions 2013-03-20_18:21:43.27647 FATAL: terminating connection due to administrator command 2013-03-20_18:22:14.25763 FATAL: the database system is shutting down 2013-03-20_18:22:14.26401 LOG: checkpoint starting: shutdown immediate 2013-03-20_18:25:36.98507 LOG: checkpoint complete: wrote 6403 buffers (2.3%); 0 transaction log file(s) added, 0 removed, 5 recycled; write=41.450 s, sync=156.157 s, total=202.943 s; sync files=50, longest=29.057 s, average=3.123 s 2013-03-20_18:25:37.65570 FATAL: the database system is shutting down 2013-03-20_18:25:37.71735 LOG: online backup mode canceled And I believe this is where the db restarted: 2013-03-20_18:25:37.71739 DETAIL: backup_label was renamed to backup_label.old. 2013-03-20_18:25:45.39001 LOG: loaded library auto_explain 2013-03-20_18:25:45.39647 LOG: loaded library pg_stat_statements 2013-03-20_18:26:04.67932 LOG: could not create socket for statistics collector: Protocol not supported 2013-03-20_18:26:04.67939 LOG: trying another address for the statistics collector 2013-03-20_18:26:05.01894 LOG: connection received: host=172.16.4.24 port=55414 2013-03-20_18:26:05.01903 FATAL: the database system is starting up 2013-03-20_18:26:05.41153 LOG: autovacuum launcher started 2013-03-20_18:26:05.41160 LOG: database system is ready to accept connections Just shy of 5 minutes to do a fast shutdown/restart. What I'm trying to determine is whether or not it's a bug for postmaster to CHECKPOINT during a fast shutdown or a documentation bug that a fast shutdown may cause a CHECKPOINT, which will delay the database being restarted. I've run in to this before and in some scripts I run a CHECKPOINT before sending a SIGINT, which reduces the size of the CHECKPOINT, but doesn't necessarily eliminate it all of the time. In fact, I'm not sure it happens 100% of the time either, but today I was bit by this shutdown/startup delay and thought I'd inquire or submit a small doc patch. Thanks in advance. -sc -- Sean Chittenden s...@chittenden.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can't terminate hung COPY
On Wed, Mar 20, 2013 at 1:12 PM, Martín Marqués martin.marq...@gmail.com wrote: What happens if you use COPY ... FROM with the same data? I will try that, but if you look at pg_stat_activity the full command is a COPY table (columns), FROM STDIN. Are you sure the process hangs (strange thing is that you can't terminate the backend)? Could it be that it looks like it hung, but it's actually COPYing a huge chunk of data? The file that psql is reading from is tiny - less than 3kB so that's definitely not the issue... -Dave -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fast Shutdown (SIGINT) results in a CHECKPOINT...
On 3/20/13 4:28 PM, Sean Chittenden wrote: For a while now I've known that sending a SIGINT can trigger a CHECKPOINT, but I don't know if this is intentional or a bug. It's intentional. If you don't want that, use SIGQUIT. That's how they are different. Of course, when using SIGQUIT, you will have to spend the time you saved on the checkpoint for the recovery at startup. So you have to put in the time either way. It is not unheard of that a shutdown can take minutes. That's why the -t option was added to pg_ctl some time ago. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] State of the art re: group default privileges
On 03/20/2013 02:24 PM, Michael Orlitzky wrote: On 03/20/2013 04:12 PM, Alvaro Herrera wrote: Michael Orlitzky wrote: I'm running into this exact situation: http://www.postgresql.org/message-id/CAG1_KcBFM0e2buUG=o7ojq_ktadrzdgd45ju7gke3duz0sz...@mail.gmail.com We really need to be able to have a group of developers who can create things and modify each others' stuff[1]. Is it still more or less impossible? The workaround that comes to mind is a script to enumerate all developers and then set the defaults one at a time. This breaks however when we add a new developer -- he can't access any of the existing stuff. I don't understand. Why doesn't alice do a set role dev_user before creating the table? Then, the table owner is dev_user, not alice, and default privileges for dev_user apply. In fact you needn't run ALTER DEFAULT PRIVILEGES at all, because dev_user will be owner of the objects, and both alice and bob have that role. It comes down to a separation of concerns. These developers shouldn't (and really, don't) know/care what the privileges should be. They don't know that they're even in a group. Why should they? As with filesystem permissions, the admin should be able to set this all up (correctly) and forget about it. What's your process? First I've heard of a group of dev's ignorant of permission _and_ trusted to change things in a db which affect others. If they are in a group, can that not define the role and go from there with std permission layouts? Are these mostly DDL changes? Might want to look at migrations tools (MyBatis, flyway and others) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can't terminate hung COPY
On Wed, Mar 20, 2013 at 12:37 PM, David Rees dree...@gmail.com wrote: PostgreSQL 8.4.16 on CentOS 5.9. I've run into a situation where executing a \COPY from psql will hang and at that point it's impossible to terminate the COPY command. Some additional notes: Running psql on the same LAN for some reason works fine with the same COPY file. On a remote network it hangs. Tried against a PostgreSQL 9.2.3 server, same results. The file that is being copied from is a single row - one of the columns has decent number of \r\n in it, deleting all of either the \r or the \n in the file allows the \COPY to succeed. I'm trying to narrow it down to a simple test case... -Dave -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can't terminate hung COPY
David Rees dree...@gmail.com writes: On Wed, Mar 20, 2013 at 12:37 PM, David Rees dree...@gmail.com wrote: PostgreSQL 8.4.16 on CentOS 5.9. I've run into a situation where executing a \COPY from psql will hang and at that point it's impossible to terminate the COPY command. Some additional notes: Running psql on the same LAN for some reason works fine with the same COPY file. On a remote network it hangs. That seems to point the finger at the network stack: what is probably happening is it's failing to abort a recv() or send() when the process receives a signal. You might see if you can confirm that diagnosis, perhaps by watching the backend process with strace, or by attaching to it after the fact with gdb and seeing where the stack trace leads. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] State of the art re: group default privileges
On 03/20/2013 05:18 PM, Rob Sargent wrote: What's your process? First I've heard of a group of dev's ignorant of permission _and_ trusted to change things in a db which affect others. It's a playground for a group of people. They want to be able to create stuff, and then modify that stuff. No one has to see it. There are no consequences to their being completely oblivious to the permissions and ownership. It's not really an outlandish use case. (rant ahead) Windows servers, you can create a share for a bunch of, say, attorneys, and let them throw WordPerfect documents in it. Any attorney can modify any document. None of them need to be system administrators or understand the implications of the permissions on newly-created files, because the admin (you) has made sure that the share is e.g. not visible to Everyone. This is done in every small business on Earth. In Unix, we have the setgid bit. If you create an attorneys group, and setgid the documents directory, then the same thing is achieved. Newly-created documents are owned by the attorneys group and so any attorney can modify it (if you set the umask properly). The permissions are up to the system administrator, nobody else has to care. MSSQL and Oracle both let you do the same thing with groups or roles or whatever they're called. If you want your permissions to be as restrictive as possible (i.e. correct), but not a huge pain in the ass, then you need to be able to grant those restrictive-as-possible permissions automatically. Nobody would put up with you if you made them manually set the permissions on every new file that they created. More importantly, if you did, most of the permissions would be screwed up, because nobody but you (our hypothetical admin) cares. They just want it to work. And chmod 777 makes it work, unless it already worked by default (my goal). My use case is the same. I have a bunch of people who want it to just work, and I'm the one who knows how the ownership and permissions should be set. I should be able to make it work for them. It's like, the canonical use case for user/group permission systems. If they are in a group, can that not define the role and go from there with std permission layouts? Not sure what you mean? They're all in the same role, but there's no way to make sure that everyone in that role can access the objects that other members create. Are these mostly DDL changes? Might want to look at migrations tools (MyBatis, flyway and others) At the moment, everyone's just experimenting. Even with the proper tooling, my blog app shouldn't have to handle the database permissions table-by-table. I should be able to set up sensible defaults. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] State of the art re: group default privileges
On 03/20/2013 03:26 PM, Michael Orlitzky wrote: On 03/20/2013 05:18 PM, Rob Sargent wrote: At the moment, everyone's just experimenting. Even with the proper tooling, my blog app shouldn't have to handle the database permissions table-by-table. I should be able to set up sensible defaults. CREATE ROLE adrian LOGIN; CREATE ROLE ranger LOGIN; CREATE ROLE dev_user ROLE; GRANT dev_user TO adrian; GRANT dev_user TO ranger; ALTER ROLE adrian IN DATABASE test set role=dev_user; aklaver@panda:~ psql -d test -U adrian Password for user adrian: psql (9.0.12) Type help for help. test= SELECT current_user; current_user -- dev_user (1 row) test= SELECT session_user; session_user -- adrian (1 row) test= CREATE TABLE adrian_tbl(id int); CREATE TABLE test= \c - ranger Password for user ranger: You are now connected to database test as user ranger. test= INSERT INTO adrian_tbl VALUES (1); INSERT 0 1 -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] State of the art re: group default privileges
On 03/20/2013 06:40 PM, Adrian Klaver wrote: On 03/20/2013 03:26 PM, Michael Orlitzky wrote: On 03/20/2013 05:18 PM, Rob Sargent wrote: At the moment, everyone's just experimenting. Even with the proper tooling, my blog app shouldn't have to handle the database permissions table-by-table. I should be able to set up sensible defaults. CREATE ROLE adrian LOGIN; CREATE ROLE ranger LOGIN; CREATE ROLE dev_user ROLE; GRANT dev_user TO adrian; GRANT dev_user TO ranger; ALTER ROLE adrian IN DATABASE test set role=dev_user; aklaver@panda:~ psql -d test -U adrian ... Thanks, this is extremely close, but doesn't quite nail it: at the end, what happens if you create a table as ranger? By default, adrian doesn't have access to it. You could of course do, ALTER ROLE ranger IN DATABASE test set role=dev_user; Now everything in the database will be owned by dev_user. But what happens if we have 100 databases (this is realistic for us), and add a new developer a year down the road? I have to not only add him to dev_user, but look through each database, figure out which ones we've used this trick on, and do, ALTER ROLE the_new_guy IN DATABASE foo set role=dev_user; And I can already achieve this result with a pile of scripts. It just feels half-assed. When I add someone to a group, they should inherit the permissions of the group. More convenient, way safer. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to join table to itself N times?
I got this table right now:
select * from market_segment_dimension_values ;
+--+---+
| market_segment_dimension | value |
+--+---+
| geography| north |
| geography| south |
| industry type| retail|
| industry type| manufacturing |
| industry type| wholesale |
+--+---+
(5 rows)
The PK is (market_segment_dimension, value).
The dimension column refers to another table called
market_segment_dimensions.
So, north and south are to values for the geography dimension.
In that data above, there are two dimensions. But sometimes there could be
just one dimension, or maybe three, ... up to ten.
Now here's the part where I'm stumped.
I need to create a cartesian product of the dimensions.
I came up with this approach by hard-coding the different dimensions:
with geog as (
select value
from market_segment_dimension_values
where market_segment_dimension = 'geography'),
industry_type as (
select value
from market_segment_dimension_values
where market_segment_dimension = 'industry type')
select geog.value as g,
industry_type.value as ind_type
from geog
cross join industry_type
;
+---+---+
| g | ind_type|
+---+---+
| north | retail|
| north | manufacturing |
| north | wholesale |
| south | retail|
| south | manufacturing |
| south | wholesale |
+---+---+
(6 rows)
But that won't work if I add a new dimension (unless I update the query).
For example, maybe I need to add a new dimension called, say, customer
size, which has values big and small. A
I've got some nasty plan B solutions, but I want to know if there's some
solution.
There's a really elegant solution in python using itertools.product, like
this:
list(itertools.product(*[['north', 'south'], ['retail',
'manufacturing', 'wholesale']]))
[('north', 'retail'),
('north', 'manufacturing'),
('north', 'wholesale'),
('south', 'retail'),
('south', 'manufacturing'),
('south', 'wholesale')]
All advice is welcome. Thanks in advance!
Matt
--
W. Matthew Wilson
m...@tplus1.com
http://tplus1.com
Re: [GENERAL] State of the art re: group default privileges
On 03/20/2013 04:11 PM, Michael Orlitzky wrote: On 03/20/2013 06:40 PM, Adrian Klaver wrote: On 03/20/2013 03:26 PM, Michael Orlitzky wrote: On 03/20/2013 05:18 PM, Rob Sargent wrote: At the moment, everyone's just experimenting. Even with the proper tooling, my blog app shouldn't have to handle the database permissions table-by-table. I should be able to set up sensible defaults. CREATE ROLE adrian LOGIN; CREATE ROLE ranger LOGIN; CREATE ROLE dev_user ROLE; GRANT dev_user TO adrian; GRANT dev_user TO ranger; ALTER ROLE adrian IN DATABASE test set role=dev_user; aklaver@panda:~ psql -d test -U adrian ... Thanks, this is extremely close, but doesn't quite nail it: at the end, what happens if you create a table as ranger? By default, adrian doesn't have access to it. You could of course do, ALTER ROLE ranger IN DATABASE test set role=dev_user; Now everything in the database will be owned by dev_user. But what happens if we have 100 databases (this is realistic for us), and add a new developer a year down the road? I have to not only add him to dev_user, but look through each database, figure out which ones we've used this trick on, and do, Not sure why everything being owned by dev_user is a problem, you said the developers don't care about permissions or want to deal with them so why does it matter what role their objects get created as? As long as developer roles inherit dev_user they get common access to the objects. Leave out the IN DATABASE and it will work for all databases in cluster. ALTER ROLE the_new_guy IN DATABASE foo set role=dev_user; And I can already achieve this result with a pile of scripts. It just feels half-assed. When I add someone to a group, they should inherit the permissions of the group. More convenient, way safer. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgresql-9.1.2 - Linux
Hi, I have installed the PG on Linux RHEL 6.3. With the DATA directory stored on a NAS Storage device which is NFS mounted on the main Linux disk. What I am seeing is that after 40-60 minutes of PG running (in the product), the 'postgres' dumps core and PG Services stops. Has anyone seen this before ? Does storing the DATA on a NAS is a problem ? Any suggestions pointers much appreciated. Thanks! Arvind
Re: [GENERAL] How to join table to itself N times?
On Wed, Mar 20, 2013 at 7:38 PM, W. Matthew Wilson m...@tplus1.com wrote:
I got this table right now:
select * from market_segment_dimension_values ;
+--+---+
| market_segment_dimension | value |
+--+---+
| geography| north |
| geography| south |
| industry type| retail|
| industry type| manufacturing |
| industry type| wholesale |
+--+---+
(5 rows)
The PK is (market_segment_dimension, value).
The dimension column refers to another table called
market_segment_dimensions.
So, north and south are to values for the geography dimension.
In that data above, there are two dimensions. But sometimes there could
be just one dimension, or maybe three, ... up to ten.
Now here's the part where I'm stumped.
I need to create a cartesian product of the dimensions.
I came up with this approach by hard-coding the different dimensions:
with geog as (
select value
from market_segment_dimension_values
where market_segment_dimension = 'geography'),
industry_type as (
select value
from market_segment_dimension_values
where market_segment_dimension = 'industry type')
select geog.value as g,
industry_type.value as ind_type
from geog
cross join industry_type
;
+---+---+
| g | ind_type|
+---+---+
| north | retail|
| north | manufacturing |
| north | wholesale |
| south | retail|
| south | manufacturing |
| south | wholesale |
+---+---+
(6 rows)
But that won't work if I add a new dimension (unless I update the query).
For example, maybe I need to add a new dimension called, say, customer
size, which has values big and small. A
I've got some nasty plan B solutions, but I want to know if there's some
solution.
There's a really elegant solution in python using itertools.product, like
this:
list(itertools.product(*[['north', 'south'], ['retail',
'manufacturing', 'wholesale']]))
[('north', 'retail'),
('north', 'manufacturing'),
('north', 'wholesale'),
('south', 'retail'),
('south', 'manufacturing'),
('south', 'wholesale')]
All advice is welcome. Thanks in advance!
Matt
--
W. Matthew Wilson
m...@tplus1.com
http://tplus1.com
You may try:
Select a.value, b.value
from market_segment_dimension_values as a,
from market_segment_dimension_values as b
where a.market_segment_dimension b.market_segment_dimension
-- AI
Re: [GENERAL] How to join table to itself N times?
On Wed, Mar 20, 2013 at 5:38 PM, W. Matthew Wilson m...@tplus1.com wrote: I got this table right now: select * from market_segment_dimension_values ; +--+---+ | market_segment_dimension | value | +--+---+ | geography| north | | geography| south | | industry type| retail| | industry type| manufacturing | | industry type| wholesale | +--+---+ (5 rows) The PK is (market_segment_dimension, value). The dimension column refers to another table called market_segment_dimensions. So, north and south are to values for the geography dimension. In that data above, there are two dimensions. But sometimes there could be just one dimension, or maybe three, ... up to ten. If the number of dimensions is not fixed, then you'll probably have to write a plpgsql function to first interrogate the data set for how many dimensions there are and then to build an n-dimension query. While joining a variable number of tables may be problematic as you won't have a fixed number of columns, using a union might give you what you want with a fixed number of columns. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql-9.1.2 - Linux
On 3/20/2013 5:29 PM, Arvind Sharma wrote: I have installed the PG on Linux RHEL 6.3. With the DATA directory stored on a NAS Storage device which is NFS mounted on the main Linux disk. What I am seeing is that after 40-60 minutes of PG running (in the product), the 'postgres' dumps core and PG Services stops. it would be interesting to get a stack trace from that core dump NFS is generally /NOT /recommended for database volumes. -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: [GENERAL] How to join table to itself N times?
Wow, this is a fun puzzle. I'd love to be the first to solve it with just SQL, but I don't have a solution yet. Here are some elements that might be useful: SELECT market_segment_dimension, array_agg(value) FROM market_segment_dimension_values GROUP BY market_segment_dimension; the UNNEST function the ROW function window functions like row_number and nth_value the crosstab function (requires installing an extension; this seems like cheating if you ask me) Good luck! Paul On Wed, Mar 20, 2013 at 7:14 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Wed, Mar 20, 2013 at 5:38 PM, W. Matthew Wilson m...@tplus1.com wrote: I got this table right now: select * from market_segment_dimension_values ; +--+---+ | market_segment_dimension | value | +--+---+ | geography| north | | geography| south | | industry type| retail| | industry type| manufacturing | | industry type| wholesale | +--+---+ (5 rows) The PK is (market_segment_dimension, value). The dimension column refers to another table called market_segment_dimensions. So, north and south are to values for the geography dimension. In that data above, there are two dimensions. But sometimes there could be just one dimension, or maybe three, ... up to ten. If the number of dimensions is not fixed, then you'll probably have to write a plpgsql function to first interrogate the data set for how many dimensions there are and then to build an n-dimension query. While joining a variable number of tables may be problematic as you won't have a fixed number of columns, using a union might give you what you want with a fixed number of columns. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- _ Pulchritudo splendor veritatis. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] State of the art re: group default privileges
On 03/20/2013 08:05 PM, Adrian Klaver wrote: Now everything in the database will be owned by dev_user. But what happens if we have 100 databases (this is realistic for us), and add a new developer a year down the road? I have to not only add him to dev_user, but look through each database, figure out which ones we've used this trick on, and do, Not sure why everything being owned by dev_user is a problem, you said the developers don't care about permissions or want to deal with them so why does it matter what role their objects get created as? As long as developer roles inherit dev_user they get common access to the objects. I must have misspoken; things being owned by dev_user is not a problem. It's that, when we have 100 databases and I add a new developer, his permissions don't really kick in automatically. I have to go back and run a command on each database to which he should have access. Since I'm going to script it, it doesn't really matter /which/ commands I need to run. So it could be SET ROLE, or ALTER DEFAULT PRIVILEGES, or whatever else. But I shouldn't need to do any of it -- adding the user to the developers group should make him a developer (in all databases where that is meaningful), and that should be the end of it. Imagine if, after adding yourself to the unix 'postgres' group, you had to go around and run a command on every file belonging to the 'postgres' group. And otherwise, you wouldn't be able to access those files. That would be weird, right? No one would want to do it, right? I don't want to do it in the database either =) Leave out the IN DATABASE and it will work for all databases in cluster. This won't fly unfortunately. It's a shared host, and the developers are a mixed bag of our employees, consultants, and the customer's employees. I do appreciate the suggestions though, so don't interpret my pessimism as lack of appreciation. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to join table to itself N times?
Okay, how about this (table names shortened):
create table m (d varchar(255) not null, v varchar(255) not null);
insert into m (d, v) values ('geography', 'north'), ('geography',
'south'), ('industry type', 'retail'), ('industry type',
'manufacturing'), ('industry type', 'wholesale');
WITH RECURSIVE t(combo, n) AS (
WITH dims AS (SELECT DISTINCT d, row_number() OVER () AS n FROM m GROUP BY d)
SELECT '{}'::text[], 1
UNION ALL
SELECT array_append(t2.combo::text[], m.v::text), t2.n+1
FROM t t2, dims
CROSS JOIN m
WHERE m.d = dims.d AND dims.n = t2.n
)
SELECT *
FROM t
WHERE n = (SELECT COUNT(DISTINCT d) + 1 FROM m);
Gives these results:
combo | n
---+---
{retail,north}| 3
{manufacturing,north} | 3
{wholesale,north} | 3
{retail,south}| 3
{manufacturing,south} | 3
{wholesale,south} | 3
(6 rows)
Paul
On Wed, Mar 20, 2013 at 8:40 PM, Paul Jungwirth
p...@illuminatedcomputing.com wrote:
Wow, this is a fun puzzle. I'd love to be the first to solve it with
just SQL, but I don't have a solution yet. Here are some elements that
might be useful:
SELECT market_segment_dimension, array_agg(value)
FROM market_segment_dimension_values
GROUP BY market_segment_dimension;
the UNNEST function
the ROW function
window functions like row_number and nth_value
the crosstab function (requires installing an extension; this seems
like cheating if you ask me)
Good luck!
Paul
On Wed, Mar 20, 2013 at 7:14 PM, Scott Marlowe scott.marl...@gmail.com
wrote:
On Wed, Mar 20, 2013 at 5:38 PM, W. Matthew Wilson m...@tplus1.com wrote:
I got this table right now:
select * from market_segment_dimension_values ;
+--+---+
| market_segment_dimension | value |
+--+---+
| geography| north |
| geography| south |
| industry type| retail|
| industry type| manufacturing |
| industry type| wholesale |
+--+---+
(5 rows)
The PK is (market_segment_dimension, value).
The dimension column refers to another table called
market_segment_dimensions.
So, north and south are to values for the geography dimension.
In that data above, there are two dimensions. But sometimes there could be
just one dimension, or maybe three, ... up to ten.
If the number of dimensions is not fixed, then you'll probably have to
write a plpgsql function to first interrogate the data set for how
many dimensions there are and then to build an n-dimension query.
While joining a variable number of tables may be problematic as you
won't have a fixed number of columns, using a union might give you
what you want with a fixed number of columns.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
_
Pulchritudo splendor veritatis.
--
_
Pulchritudo splendor veritatis.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] Trust intermediate CA for client certificates
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/19/2013 09:46 PM, Stephen Frost wrote: * Craig Ringer (cr...@2ndquadrant.com) wrote: As far as I'm concerned that's the immediate problem fixed. It may be worth adding a warning on startup if we find non-self-signed certs in root.crt too, something like 'WARNING: Intermediate certificate found in root.crt. This does not do what you expect and your configuration may be insecure; see the Client Certificates chapter in the documentation.' I'm not sure that I follow this logic, unless you're proposing that intermediate CAs only be allowed to be picked up from system-wide configuration? That strikes me as overly constrained as I imagine there are valid configurations today which have intermediate CAs listed, with the intention that they be available for PG to build the chain from a client cert that is presented back up to the root. Now, the client might be able to provide such an intermediate CA cert too (one of the fun things about SSL is that the client can send any 'missing' certs to the server, if it has them available..), but it also might not. Drat, you're quite right. I've always included the full certificate chain in client certs but it's in no way required. I guess that pretty much means mainaining the status quo and documenting it better. - -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.13 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQEcBAEBAgAGBQJRSp3fAAoJELBXNkqjr+S2+JYH+wUo2mCMB2n3/mXo24l0rO5+ mxS6d9uJNIZZErZX2I/NfY59kLX1ypUAeGhQnCSOZuxig6Xd91nXzRdkaQF/+WHa 9hEAXbOtl7bMgj8cEIfloQlSU94VXamH53i5YL5ZVLqkQG/7uknY05NbJs3IGM5g ALrEgo3XOC8JyUz21hZzaQOb2vbdSh0F0O17EoJz1fLY6l5ScFnLWihKYurp5Oq0 em1bsN0GKckmSa7a9mJ37Hvowi92epbtF4XR1DyrQGOHQSCLq0NnCthA5MtdPXN0 +BJQWZfx0qcRcrHMILkFa0Uu7Bc9Ao0q06l55DNSyYXx1FWN0cBArGpXcoPb8Zs= =BAYd -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
