Re: [GENERAL] Postgres as key/value store
snacktime wrote on 28.09.2014 01:48: I'm looking for some feedback on the design I'm using for a basic key/value storage using postgres. Are you aware of Postgres' "native" key/value store: hstore? http://www.postgresql.org/docs/current/static/hstore.html Or Postgres JSON support? Especially the new JSONB in the upcoming 9.4 release which makes indexing JSON documents much eaasier/faster. I would expect both solutions to be much faster than your entity-attribute-value design. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] call pl/pgsql function from main pl/pgsql function
Hi All I have one main function in pl/pgsql and one subset function in pl/pgsql . i need to call Subset Function From main function and i do not need to wait for subset Function return. I just need to send parameter to subset function and do not need any thing in return. So i can call multiply subset function repeatedly. Any one can help with this problem ?
Re: [GENERAL] call pl/pgsql function from main pl/pgsql function
Mehdi Ravanbakhsh wrote > Hi All > > I have one main function in pl/pgsql and one subset function in pl/pgsql > . > > i need to call Subset Function From main function and i do not need to > wait for subset Function return. I just need to send parameter to subset > function and do not need any thing in return. > > So i can call multiply subset function repeatedly. > > Any one can help with this problem ? All functions are synchronous. Best you can do is write the sub function to return quickly by, for instance, having it simply write to a table and have a task processor handle the work in a separate session. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/call-pl-pgsql-function-from-main-pl-pgsql-function-tp5820818p5820820.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] PANIC: could not create file "pg_xlog/xlogtemp.7884": No space left on device
Thanks Raymond. But I think the symptoms that I am seeing are different though I get the no disk space issue in the following ways: 1. There is enough disk space on my device (output of df -h), 300M is used from 56G space. /dev/mapper/data1-object--cache 58G 300M 58G 1% /object-cache1 2. ls -rtl pg_xlog: drwx-- 2 postgres 75 4096 Sep 28 03:45 archive_status -rw--- 1 postgres 75 16777216 Sep 28 04:33 0001000B -rw--- 1 postgres 75 16777216 Sep 28 04:34 0001000C ( I see the WAL xlog files are getting created and the older ones are getting deleted correctly and they are using hardly 32MB from a partition of 58G so where is the question of no space) 3. Could this be related to: http://www.postgresql.org/message-id/20140414014442.15385.74...@wrigleys.postgresql.org. Here there is mention of 'long writes and short writes', will trying out latest version of postgress help? 4. I tried with 'archive_command = /usr/bin.true' but still I face the ""pg_xlog/xlogtemp.7884": No space left on device" issue. 5. Once I hit ""pg_xlog/xlogtemp.7884": No space left on device" I start seeing it for other files as well: "WARNING: could not create relation-cache initialization file "base/16384/pg_internal.init.13715": No space left on device DETAIL: Continuing anyway, but there's something wrong." 6. I am seeing this issue when I create around 60,000 to 70,000 files (each of size 4k) in the data base. Appreciate any quick help on this. -- Thanks and regards, Rupesh -Original Message- From: Raymond O'Donnell [mailto:r...@iol.ie] Sent: Friday, September 26, 2014 9:41 PM To: Roopeshakumar Narayansa Shalgar (rshalgar); 'PostgreSQL' Subject: Re: [GENERAL] PANIC: could not create file "pg_xlog/xlogtemp.7884": No space left on device On 25/09/2014 11:44, Roopeshakumar Narayansa Shalgar (rshalgar) wrote: > Hi, > > > > I am using version 9.3.1 and see the "no space device error" even though > there is enough space (99% free) on my disk. > > > > Error: PANIC: could not create file "pg_xlog/xlogtemp.7884": No space > left on device > > > > I want to know whether this is a bug and if yes whether fix or > workaround is available. It's not a bug - you're out of disk space. As it happens, I saw a blog post yesterday on precisely this problem, and approaches to fixing it: http://blog.endpoint.com/2014/09/pgxlog-disk-space-problem-on-postgres.html HTH, Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] call pl/pgsql function from main pl/pgsql function
Plain pg function is synchronization,the main function must wait for the sub function over. I rember dblink can send asynchronous query,maybe you can wrap your sub function with dblink.but transition behavior should be careful thought. 2014年9月28日 8:25 PM于 "Mehdi Ravanbakhsh" 写道： > Hi All > > I have one main function in pl/pgsql and one subset function in pl/pgsql . > > i need to call Subset Function From main function and i do not need to > wait for subset Function return. I just need to send parameter to subset > function and do not need any thing in return. > > So i can call multiply subset function repeatedly. > > Any one can help with this problem ? >
Re: [GENERAL] PANIC: could not create file "pg_xlog/xlogtemp.7884": No space left on device
On 09/28/2014 05:52 AM, Roopeshakumar Narayansa Shalgar (rshalgar) wrote: Thanks Raymond. But I think the symptoms that I am seeing are different though I get the no disk space issue in the following ways: 1. There is enough disk space on my device (output of df -h), 300M is used from 56G space. /dev/mapper/data1-object--cache 58G 300M 58G 1% /object-cache1 2. ls -rtl pg_xlog: drwx-- 2 postgres 75 4096 Sep 28 03:45 archive_status -rw--- 1 postgres 75 16777216 Sep 28 04:33 0001000B -rw--- 1 postgres 75 16777216 Sep 28 04:34 0001000C ( I see the WAL xlog files are getting created and the older ones are getting deleted correctly and they are using hardly 32MB from a partition of 58G so where is the question of no space) 3. Could this be related to: http://www.postgresql.org/message-id/20140414014442.15385.74...@wrigleys.postgresql.org. Here there is mention of 'long writes and short writes', will trying out latest version of postgress help? Well in the bug report the issue seems to be with BLCKSZ and XLOG_BLCKSZ. So have you changed these? I see the commit in my 9.3.5 sources, so if the above is indeed the issue then using the latest version would help. If that does not work then more information is needed. What file system, OS, architecture(32/64bit) are you using? What are the files you refer to below and how are they being created/inserted? 4. I tried with 'archive_command = /usr/bin.true' but still I face the ""pg_xlog/xlogtemp.7884": No space left on device" issue. 5. Once I hit ""pg_xlog/xlogtemp.7884": No space left on device" I start seeing it for other files as well: "WARNING: could not create relation-cache initialization file "base/16384/pg_internal.init.13715": No space left on device DETAIL: Continuing anyway, but there's something wrong." 6. I am seeing this issue when I create around 60,000 to 70,000 files (each of size 4k) in the data base. Appreciate any quick help on this. -- Thanks and regards, Rupesh -- Adrian Klaver adrian.kla...@aklaver.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] how to see "where" SQL is better than PLPGSQL
Hi all. I see an entire database, with all the stored procedures writen in plpgsql. Off course, many (if not all) of that SP are simple inserts, updates, selects and so on. So, i want to test and show the differences between doing the same function in pgpgsql vs. plain sql. Im getting statistics (via collectd if that matters) and doing a modified version of the pgbench tests, just using pl (and sql) functions instead of the plain query: \setrandom delta -5000 5000 BEGIN; SELECT pgbench_accounts_upd_pl(:delta, :aid); SELECT get_pgbench_accounts_pl(:aid); SELECT pgbench_tellers_upd_pl(:delta, :tid); SELECT pgbench_branches_upd_pl(:delta, :bid); select pgbench_history_ins_pl(:tid, :bid, :aid, :delta); END; At first, pgbench is showing a difference between the "pl" and de "sql" versions: (pl.scripts own the "PL" version, sql.script owns the "SQL" version of the test) (This is a tiny netbook, with a dual core procesor) gherzig@via:~> pgbench -c 2 -C -T 300 -f pl.script -U postgres test duration: 300 s number of transactions actually processed: 13524 tps = 45.074960 (including connections establishing) tps = 75.260741 (excluding connections establishing) gherzig@via:~> pgbench -c 2 -C -T 300 -f sql.script -U postgres test starting vacuum...end. duration: 300 s number of transactions actually processed: 15125 tps = 50.412852 (including connections establishing) tps = 92.058245 (excluding connections establishing) So yeah, it looks like the "SQL" version is able to do a 10% more transactions. However, i was hoping to see anothers "efects" of using sql (perhaps less load avg in the SQL version), at the OS level. So, finnaly, the actual question: ¿Wich signals should i monitor, in order to show that PGPLSQL uses more resources than SQL? Thanks! Gerardo -- 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] [SQL] how to see "where" SQL is better than PLPGSQL
2014-09-28 20:30 GMT+02:00 Gerardo Herzig : > Hi all. I see an entire database, with all the stored procedures writen in > plpgsql. Off course, many (if not all) of that SP are simple inserts, > updates, selects and so on. > > So, i want to test and show the differences between doing the same > function in pgpgsql vs. plain sql. > Im getting statistics (via collectd if that matters) and doing a modified > version of the pgbench tests, just using pl (and sql) functions instead of > the plain query: > > \setrandom delta -5000 5000 > BEGIN; > SELECT pgbench_accounts_upd_pl(:delta, :aid); > SELECT get_pgbench_accounts_pl(:aid); > SELECT pgbench_tellers_upd_pl(:delta, :tid); > SELECT pgbench_branches_upd_pl(:delta, :bid); > select pgbench_history_ins_pl(:tid, :bid, :aid, :delta); > END; > > At first, pgbench is showing a difference between the "pl" and de "sql" > versions: > > (pl.scripts own the "PL" version, sql.script owns the "SQL" version of the > test) > (This is a tiny netbook, with a dual core procesor) > > gherzig@via:~> pgbench -c 2 -C -T 300 -f pl.script -U postgres test > duration: 300 s > number of transactions actually processed: 13524 > tps = 45.074960 (including connections establishing) > tps = 75.260741 (excluding connections establishing) > > gherzig@via:~> pgbench -c 2 -C -T 300 -f sql.script -U postgres test > starting vacuum...end. > duration: 300 s > number of transactions actually processed: 15125 > tps = 50.412852 (including connections establishing) > tps = 92.058245 (excluding connections establishing) > > So yeah, it looks like the "SQL" version is able to do a 10% more > transactions. > However, i was hoping to see anothers "efects" of using sql (perhaps less > load avg in the SQL version), at the OS level. > > So, finnaly, the actual question: > ¿Wich signals should i monitor, in order to show that PGPLSQL uses more > resources than SQL? > It is hard question. It is invisible feature of SQL proc - inlining. What I know, a SQL function is faster than PLpgSQL function, when it is inlined. But there is nothing visible metric, that inform you about inlining. Regards Pavel > > Thanks! > Gerardo > > > -- > Sent via pgsql-sql mailing list (pgsql-...@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [GENERAL] PANIC: could not create file "pg_xlog/xlogtemp.7884": No space left on device
Adrian Klaver writes: > On 09/28/2014 05:52 AM, Roopeshakumar Narayansa Shalgar (rshalgar) wrote: >> 4. I tried with 'archive_command = /usr/bin.true' but still I face the >> ""pg_xlog/xlogtemp.7884": No space left on device" issue. This error message is pretty specific: the OS is telling us we can't have any more disk space. If you're sure you're not out of disk space, then the likely explanation is there's some sort of disk quota in effect. 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] [SQL] how to see "where" SQL is better than PLPGSQL
> Hi all. I see an entire database, with all the stored procedures > writen in plpgsql. Off course, many (if not all) of that SP are > simple inserts, updates, selects and so on. > > So, i want to test and show the differences between doing the same > function in pgpgsql vs. plain sql. > Im getting statistics (via collectd if that matters) and doing a > modified version of the pgbench tests, just using pl (and sql) > functions instead of the plain query: > > \setrandom delta -5000 5000 > BEGIN; > SELECT pgbench_accounts_upd_pl(:delta, :aid); > SELECT get_pgbench_accounts_pl(:aid); > SELECT pgbench_tellers_upd_pl(:delta, :tid); > SELECT pgbench_branches_upd_pl(:delta, :bid); > select pgbench_history_ins_pl(:tid, :bid, :aid, :delta); > END; > > At first, pgbench is showing a difference between the "pl" and de > "sql" versions: > > (pl.scripts own the "PL" version, sql.script owns the "SQL" version > of the test) > (This is a tiny netbook, with a dual core procesor) > > gherzig@via:~> pgbench -c 2 -C -T 300 -f pl.script -U postgres test > duration: 300 s > number of transactions actually processed: 13524 > tps = 45.074960 (including connections establishing) > tps = 75.260741 (excluding connections establishing) > > gherzig@via:~> pgbench -c 2 -C -T 300 -f sql.script -U postgres test > starting vacuum...end. > duration: 300 s > number of transactions actually processed: 15125 > tps = 50.412852 (including connections establishing) > tps = 92.058245 (excluding connections establishing) > > So yeah, it looks like the "SQL" version is able to do a 10% more > transactions. > However, i was hoping to see anothers "efects" of using sql (perhaps > less load avg in the SQL version), at the OS level. > > So, finnaly, the actual question: > ¿Wich signals should i monitor, in order to show that PGPLSQL uses > more resources than SQL? > > > > It is hard question. It is invisible feature of SQL proc - inlining. > What I know, a SQL function is faster than PLpgSQL function, when it > is inlined. But there is nothing visible metric, that inform you > about inlining. > > > Regards > > > Pavel > > Thanks Pavel! Im not (directly) concerned about speed, im concerned about resources usage. May be there is a value that shows the "PGSQL machine necesary for plpgsql execution" Thanks again for your time. Gerardo -- 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] [SQL] how to see "where" SQL is better than PLPGSQL
2014-09-28 21:29 GMT+02:00 Gerardo Herzig : > > Hi all. I see an entire database, with all the stored procedures > > writen in plpgsql. Off course, many (if not all) of that SP are > > simple inserts, updates, selects and so on. > > > > So, i want to test and show the differences between doing the same > > function in pgpgsql vs. plain sql. > > Im getting statistics (via collectd if that matters) and doing a > > modified version of the pgbench tests, just using pl (and sql) > > functions instead of the plain query: > > > > \setrandom delta -5000 5000 > > BEGIN; > > SELECT pgbench_accounts_upd_pl(:delta, :aid); > > SELECT get_pgbench_accounts_pl(:aid); > > SELECT pgbench_tellers_upd_pl(:delta, :tid); > > SELECT pgbench_branches_upd_pl(:delta, :bid); > > select pgbench_history_ins_pl(:tid, :bid, :aid, :delta); > > END; > > > > At first, pgbench is showing a difference between the "pl" and de > > "sql" versions: > > > > (pl.scripts own the "PL" version, sql.script owns the "SQL" version > > of the test) > > (This is a tiny netbook, with a dual core procesor) > > > > gherzig@via:~> pgbench -c 2 -C -T 300 -f pl.script -U postgres test > > duration: 300 s > > number of transactions actually processed: 13524 > > tps = 45.074960 (including connections establishing) > > tps = 75.260741 (excluding connections establishing) > > > > gherzig@via:~> pgbench -c 2 -C -T 300 -f sql.script -U postgres test > > starting vacuum...end. > > duration: 300 s > > number of transactions actually processed: 15125 > > tps = 50.412852 (including connections establishing) > > tps = 92.058245 (excluding connections establishing) > > > > So yeah, it looks like the "SQL" version is able to do a 10% more > > transactions. > > However, i was hoping to see anothers "efects" of using sql (perhaps > > less load avg in the SQL version), at the OS level. > > > > So, finnaly, the actual question: > > ¿Wich signals should i monitor, in order to show that PGPLSQL uses > > more resources than SQL? > > > > > > > > It is hard question. It is invisible feature of SQL proc - inlining. > > What I know, a SQL function is faster than PLpgSQL function, when it > > is inlined. But there is nothing visible metric, that inform you > > about inlining. > > > > > > Regards > > > > > > Pavel > > > > > Thanks Pavel! Im not (directly) concerned about speed, im concerned about > resources usage. > May be there is a value that shows the "PGSQL machine necesary for plpgsql > execution" > This is little bit more wide topic. The performance is only one point, second is a readability, robustness, .. and there are questions about plan caching, query optimization, Usually, PLpgSQL should not be used for one line SELECT based functions or one line expression based functions. But there are some exceptions. The best way is slow queries monitoring, and slow queries analyse - it is base for decision for changing language. Regards Pavel > > Thanks again for your time. > Gerardo >
[GENERAL] table versioning approach (not auditing)
Hi. I need to maintain a record of all changes to certain tables so assist in viewing history and reverting changes when necessary (customer service makes an incorrect edit, etc.). I have studied these two audit trigger examples: https://wiki.postgresql.org/wiki/Audit_trigger https://wiki.postgresql.org/wiki/Audit_trigger_91plus I've also read about two other approaches to versioning: 1. maintain all versions in one table, with a flag to indicate which is the current version 2. have a separate versions table for each real table, and insert into the associated version table whenever an update or insert is done. My current implementation is based on the wiki trigger examples, using a single table, and a json column to record the row changes (rather than hstore). What I like about that, in particular, is I can have a "global," chronological view of all versioned changes very easily. But there are two types of queries I need to run. 1. Find all changes made by a specific user 2. Find all changes related to a specific record #1 is simple to do. The versioning table has a user_id column of who made the change, so I can query on that. #2 is more difficult. I may want to fetch all changes to a group of tables that are all related by foreign keys (e.g., find all changes to "user" record 849, along with any changes to their "articles," "photos," etc.). All of the data is in the json column, of course, but it seems like a pain to try and build a query on the json column that can fetch all those relationships (and if I mess it up, I probably won't generate any errors, since the json is so free-form). So my question is, do you think using the json approach is wrong for this case? Does it seem better to have separate versioning tables associated with each real table? Or another approach? Thanks
Re: [GENERAL] table versioning approach (not auditing)
On 29/09/14 15:00, Abelard Hoffman wrote: Hi. I need to maintain a record of all changes to certain tables so assist in viewing history and reverting changes when necessary (customer service makes an incorrect edit, etc.). I have studied these two audit trigger examples: https://wiki.postgresql.org/wiki/Audit_trigger https://wiki.postgresql.org/wiki/Audit_trigger_91plus I've also read about two other approaches to versioning: 1. maintain all versions in one table, with a flag to indicate which is the current version 2. have a separate versions table for each real table, and insert into the associated version table whenever an update or insert is done. My current implementation is based on the wiki trigger examples, using a single table, and a json column to record the row changes (rather than hstore). What I like about that, in particular, is I can have a "global," chronological view of all versioned changes very easily. But there are two types of queries I need to run. 1. Find all changes made by a specific user 2. Find all changes related to a specific record #1 is simple to do. The versioning table has a user_id column of who made the change, so I can query on that. #2 is more difficult. I may want to fetch all changes to a group of tables that are all related by foreign keys (e.g., find all changes to "user" record 849, along with any changes to their "articles," "photos," etc.). All of the data is in the json column, of course, but it seems like a pain to try and build a query on the json column that can fetch all those relationships (and if I mess it up, I probably won't generate any errors, since the json is so free-form). So my question is, do you think using the json approach is wrong for this case? Does it seem better to have separate versioning tables associated with each real table? Or another approach? Thanks I implemented a 2 table approach over 15 years ago for an insurance application. I used both an /effective_date/ & and an /as_at_date/, no triggers were involved. I think a 2 table approach gives you more flexibility. The /effective_date/ allowed changes to be made to the table in advance of when they were to become effective. The /as_at_date/ allowed quotes to be made, valid for a period starting at the as_at_date. End users did not query the database directly, all queries were precoded in a 4GL called Progress backed by an Oracle database. The same could be done with a WildFly Java Enterprise AppSever (or some other middleware) and a PostgreSQL backend. Different use case, but the concept is probably adaptable to your situation. You may want a change table, that has a change_number that is in each type of table affected by a change. This would help for query type #2. I would be quite happy to contract to work out the appropriate schema and develop some SQL scripts to query & update the database, if you were interested. My approach would be to create a minimal database with sample data to validate the schema design and SQL scripts. Using a flag to indicate current record, seems inflexible. As some changes may not take affect until some time in the future, and you can't query the database to see what was the situation at a particular point in the past. For example: somebody complains about something that happened last Saturday near noon, how would you query the database to what it was like then? Cheers, Gavin
