Re: [HACKERS] System catalog vacuum issues
On 08/16/2013 10:44 AM, Vlad Arkhipov wrote: On 08/15/2013 03:27 AM, Jim Nasby wrote: On 8/14/13 12:31 AM, Vlad Arkhipov wrote: I used to use VACUUM FULL periodically to resolve the issue, but the problem arises again in 2-3 months. Here is the statistics (from pgstattuple). I run VACUUM FULL on 2013-08-07. How much non-temporary DDL do you do? It's possible that you end up with a tuple at the end of the table for a non-temporary object. One of those would stay valid for quite some time, and if you're unlucky then you'll end up with another long-lived row farther down the table, etc, etc. Depending on how frequently you're creating temp objects, autovac might not be able to keep up. Assuming that a manual vacuum doesn't take too long it might be a good idea to cron a manual vacuum (NOT FULL) of that table once a minute. Not much. 1-2 tables per day. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] System catalog vacuum issues
On 08/15/2013 05:06 AM, Sergey Konoplev wrote: On Tue, Aug 13, 2013 at 10:31 PM, Vlad Arkhipov arhi...@dc.baikal.ru wrote: I used to use VACUUM FULL periodically to resolve the issue, but the problem arises again in 2-3 months. Here is the statistics (from pgstattuple). I run VACUUM FULL on 2013-08-07. date| relpages | reltuples | table_len | tuple_count | tuple_percent | dead_tuple_count | dead_tuple_len | free_space | free_percent | autovacuum_count +--+---+---+-+---+--+++--+-- 2013-08-08 |39029 |109096 | 319725568 | 37950 | 1.66 |52540 |7355600 | 296440048 |92.72 | Are you sure you did VACUUM FULL pg_attribute on Aug 7, could you please confirm that free_percent arises from 0 to 92% in one day? Do you have some processes that intensively create tables or columns and then delete them or create them in transaction and rollback the transaction? Absolutely. Here is 1-minute statistics on this table. VACUUM FULL was done on 2013-08-16 00:35:00. There are many processes that create and drop temporary tables. time | reltuples | table_len | tuple_count | tuple_percent | dead_tuple_count | dead_len | free_space | free_percent | autovacuum_count ---+-+---+-+---+--+++--+-- 2013-08-16 00:33:01.977405+09 | 1.50021e+07 | 2211 MB | 38981 | 0.24 | 15505917 | 2070 MB| 8339 kB | 0.37 | 7463 2013-08-16 00:34:01.718696+09 | 1.50021e+07 | 2211 MB | 38875 | 0.23 | 15505952 | 2070 MB| 8349 kB | 0.37 | 7463 2013-08-16 00:35:01.570965+09 | 38875 | 5664 kB | 38875 | 93.84 | 46 | 6440 bytes | 19 kB | 0.34 | 7463 2013-08-16 00:36:01.658131+09 | 38875 | 5664 kB | 38875 | 93.84 | 46 | 6440 bytes | 19 kB | 0.34 | 7463 ... 2013-08-16 08:10:01.201473+09 | 47950 | 52 MB | 47685 | 12.22 | 318481 | 43 MB | 229 kB | 0.43 | 7493 2013-08-16 08:11:01.411891+09 | 47950 | 54 MB | 47776 | 11.86 | 329589 | 44 MB | 333 kB | 0.6 | 7493 2013-08-16 08:12:01.623495+09 | 48036 | 56 MB | 47816 | 11.47 | 343932 | 46 MB | 199 kB | 0.35 | 7495 2013-08-16 08:13:01.837192+09 | 48036 | 58 MB | 47903 | 11.11 | 356488 | 48 MB | 286 kB | 0.48 | 7495 2013-08-16 08:14:02.041228+09 | 48036 | 59 MB | 47899 | 10.82 | 366939 | 49 MB | 370 kB | 0.61 | 7495 2013-08-16 08:15:01.254325+09 | 48036 | 61 MB | 48065 | 10.61 | 376192 | 50 MB | 420 kB | 0.68 | 7495 2013-08-16 08:16:01.557785+09 | 48210 | 62 MB | 48290 | 10.36 | 386019 | 52 MB | 696 kB | 1.09 | 7496 2013-08-16 08:17:01.774188+09 | 48210 | 64 MB | 48330 | 10.14 | 392236 | 52 MB | 1188 kB | 1.82 | 7496 2013-08-16 08:18:01.977503+09 | 48210 | 65 MB | 48370 | 9.87 |79643 | 11 MB | 46 MB |70.07 | 7496 2013-08-16 08:19:01.154589+09 | 48210 | 68 MB | 48550 | 9.55 |27483 | 3757 kB| 55 MB |81.55 | 7496 2013-08-16 08:20:01.321973+09 | 48333 | 69 MB | 48694 | 9.41 |42512 | 5812 kB| 54 MB |78.83 | 7497 2013-08-16 08:21:01.48612+09 | 48333 | 69 MB | 48831 | 9.43 |43172 | 5902 kB| 54 MB |78.67 | 7497 2013-08-16 08:22:01.668103+09 | 48926 | 69 MB | 48947 | 9.46 |22677 | 3100 kB| 57 MB |82.72 | 7498 2013-08-16 08:23:01.83524+09 | 48962 | 69 MB | 48914 | 9.45 | 8655 | 1183 kB| 59 MB | 85.5 | 7499 ... 2013-08-16 10:22:01.590888+09 | 52114 | 131 MB| 52395 | 5.33 | 866015 | 116 MB | 1045 kB| 0.78 | 7550 2013-08-16 10:23:01.908792+09 | 52114 | 133 MB| 52579 | 5.29 | 560495 | 75 MB| 44 MB | 33.44 | 7550 2013-08-16 10:24:01.207538+09 | 52114 | 134 MB| 52566 | 5.22 | 222138 | 30 MB| 92 MB | 68.77 | 7550 2013-08-16 10:25:01.485565+09 | 52114 | 136 MB| 52637 | 5.17 |25493 | 3485 kB
Re: [HACKERS] System catalog vacuum issues
I used to use VACUUM FULL periodically to resolve the issue, but the
problem arises again in 2-3 months.
Here is the statistics (from pgstattuple). I run VACUUM FULL on 2013-08-07.
dcdb=# select date, relpages, reltuples, table_len, tuple_count,
tuple_percent, dead_tuple_count, dead_tuple_len, free_space,
free_percent, autovacuum_count from public.table_statistics where
relname = 'pg_attribute' order by date;
date| relpages | reltuples | table_len | tuple_count |
tuple_percent | dead_tuple_count | dead_tuple_len | free_space |
free_percent | autovacuum_count
+--+---+---+-+---+--+++--+--
2013-08-08 |39029 |109096 | 319725568 | 37950 |
1.66 |52540 |7355600 | 296440048 |92.72
| 6359
2013-08-09 |12382 | 95848 | 101433344 | 38232 |
5.28 |57443 |8042020 | 83862864 |82.68
| 6711
2013-08-10 |11365 |105073 | 93102080 | 37789 |
5.68 |65599 |9183860 | 74483104 | 80
| 7002
2013-08-12 | 9447 | 95289 | 77389824 | 37811 |
6.84 |57154 |8001560 | 60479736 |78.15
| 7161
2013-08-13 |47841 | 82877 | 391913472 | 38536 |
1.38 |30461 |4264540 | 369093756 |94.18
| 7347
2013-08-14 |70265 |104926 | 575610880 | 38838 |
0.94 |34649 |4850860 | 546449480 |94.93
| 7398
(6 rows)
Autovacuum is running on this table, however it keeps growing.
On 08/06/2013 09:35 PM, Tom Lane wrote:
Vlad Arkhipov arhi...@dc.baikal.ru writes:
On 08/06/2013 04:26 PM, Sergey Konoplev wrote:
What pgstattuple shows on this table?
dcdb=# select * from pgstattuple('pg_catalog.pg_attribute');
table_len | tuple_count | tuple_len | tuple_percent |
dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space |
free_percent
+-+---+---+--++++--
6363938816 | 48786 | 6830040 | 0.11 | 1459439 |
204321460 | 3.21 | 5939017376 | 93.32
(1 row)
So the problem isn't so much that you have lots of dead tuples, it's that
the file is full of free space. I suspect the key issue is that
autovacuum is unable to truncate the file because of too many concurrent
accesses. There was a fix in 9.2.3 that was meant to ameliorate that
problem, but maybe that's not getting the job done for you. Or maybe the
bloat we're looking at is left over from when you were running earlier
9.2.x releases; in which case a one-time VACUUM FULL should fix it.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] System catalog vacuum issues
On 08/06/2013 04:00 PM, Craig Ringer wrote: On 08/06/2013 01:56 PM, Vlad Arkhipov wrote: Hello, We are suffering from a long-standing issue with autovacuuming/vacuuming system catalogs on the production server. We are actively using temporary tables in the legacy application, so system catalogs grows unbounded in time. Autovacuum does not remove dead tuples and neither do the manual vacuum. We are running PostgreSQL 9.2.4 on Linux 2.6.18 x86_64. Nobody's holding an open transaction for long periods. Got any prepared transactions? SELECT * FROM pg_prepared_xacts; SHOW max_prepared_transactions; dcdb=# select * from pg_prepared_xacts; transaction | gid | prepared | owner | database -+-+--+---+-- (0 rows) dcdb=# show max_prepared_transactions; max_prepared_transactions --- 100 (1 row) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] System catalog vacuum issues
On 08/06/2013 04:26 PM, Sergey Konoplev wrote:
On Mon, Aug 5, 2013 at 10:56 PM, Vlad Arkhipov arhi...@dc.baikal.ru wrote:
dcdb=# select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
from pg_stat_sys_tables where relname = 'pg_attribute';
relname| n_live_tup | n_dead_tup | last_vacuum |
last_autovacuum
--+++---+---
pg_attribute | 39318086 | 395478 | 2013-08-06 14:47:48.187259+09 |
2013-08-06 13:43:03.162286+09
What pgstattuple shows on this table?
http://www.postgresql.org/docs/9.2/interactive/pgstattuple.html
dcdb=# select * from pgstattuple('pg_catalog.pg_attribute');
table_len | tuple_count | tuple_len | tuple_percent |
dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space |
free_percent
+-+---+---+--++++--
6363938816 | 48786 | 6830040 | 0.11 | 1459439 |
204321460 | 3.21 | 5939017376 | 93.32
(1 row)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] System catalog vacuum issues
Hello, We are suffering from a long-standing issue with autovacuuming/vacuuming system catalogs on the production server. We are actively using temporary tables in the legacy application, so system catalogs grows unbounded in time. Autovacuum does not remove dead tuples and neither do the manual vacuum. We are running PostgreSQL 9.2.4 on Linux 2.6.18 x86_64. Nobody's holding an open transaction for long periods. dcdb=# select xact_start, query_start, state, query from pg_stat_activity where state 'idle'; xact_start | query_start | state | query ---+---++--- 2013-08-06 14:46:56.303261+09 | 2013-08-06 14:46:56.303261+09 | active | select xact_start, query_start, state, query from pg_stat_activity where state 'idle'; (1 row) dcdb=# select count(*) from pg_attribute; count --- 51279 (1 row) dcdb=# select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_sys_tables where relname = 'pg_attribute'; relname| n_live_tup | n_dead_tup | last_vacuum | last_autovacuum --+++---+--- pg_attribute | 39318143 | 427798 | 2013-08-06 14:46:09.323187+09 | 2013-08-06 13:43:03.162286+09 (1 row) dcdb=# vacuum analyze verbose pg_attribute; INFO: vacuuming pg_catalog.pg_attribute INFO: index pg_attribute_relid_attnam_index now contains 492122 row versions in 166671 pages DETAIL: 0 index row versions were removed. 163952 index pages have been deleted, 162834 are currently reusable. CPU 0.69s/0.21u sec elapsed 0.94 sec. INFO: index pg_attribute_relid_attnum_index now contains 492253 row versions in 118119 pages DETAIL: 0 index row versions were removed. 116071 index pages have been deleted, 115269 are currently reusable. CPU 0.51s/0.14u sec elapsed 0.67 sec. INFO: pg_attribute: found 0 removable, 460354 nonremovable row versions in 9570 out of 776848 pages DETAIL: 440706 dead row versions cannot be removed yet. There were 1788424 unused item pointers. 0 pages are entirely empty. CPU 1.28s/0.43u sec elapsed 1.75 sec. INFO: analyzing pg_catalog.pg_attribute INFO: pg_attribute: scanned 3 of 776848 pages, containing 1918 live rows and 15226 dead rows; 1878 rows in sample, 39318086 estimated total rows VACUUM dcdb=# select relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum from pg_stat_sys_tables where relname = 'pg_attribute'; relname| n_live_tup | n_dead_tup | last_vacuum | last_autovacuum --+++---+--- pg_attribute | 39318086 | 395478 | 2013-08-06 14:47:48.187259+09 | 2013-08-06 13:43:03.162286+09 (1 row) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporal features in PostgreSQL
Hi, On 02/15/2013 10:46 PM, Cédric Villemain wrote: Hello, I'm also interested in this topic. I'm also interested in this topic and work on system-time temporal extension. Here I wrote down design of my solution few months ago https://wiki.postgresql.org/wiki/SQL2011Temporal. The idea is basically the same as in your solution with some minor differences. I've added a requirement in the system here: the table to be versioned must have a PK (I dislike _entry_id usage but this sounds good othwise). I then define a EXCLUDE WITH GIST (pk with =, sys_period with ), thus getting expected UNIQUEness also in the history. I use similar constraints for application-time period tables but not for system versioned. Because they are automatically controlled by a trigger, there should be no need for additional integrity checks. If you want to speed up queries against historical data, you can create GIST index or an exclusion constraint. Vlad, is your source code in a public versionning system (github, bucket, etc) ? It will ease the process to participate to your extension... Yes, I uploaded it on github https://github.com/arkhipov/temporal_tables/ The extension is also available on PGXN http://pgxn.org/dist/temporal_tables/1.0.0/ -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation
Re: [HACKERS] Temporal features in PostgreSQL
On 02/04/2013 07:40 PM, Miroslav Šimulčík wrote: Hi Vlad, I'm also interested in this topic and work on system-time temporal extension. Here I wrote down design of my solution few months ago https://wiki.postgresql.org/wiki/SQL2011Temporal. The idea is basically the same as in your solution with some minor differences. For example: - I use after triggers to store old versions of rows into historical table, so the row is archived only if modification is actualy executed. Then other BEFORE triggers are not able to see what time is going to be inserted into the table. I considered using two triggers, BEFORE trigger for setting the period and AFTER trigger for archiving rows into the history table, but did not find any use cases when it can be better than just a simple BEFORE trigger. - I don't need to deal with update conflicts, because I use clock_timestamp() instead of current_timestamp. You can still come across a conflict even with clock_timestamp(). What if clocks go back during the time synchronization? Even if you have absolutely precious clocks, there are may be clock skew on different CPUs, low system clock time resolution, etc. Although my solution needs changes in parser to stick with SQL 2011 standard, maybe you can find something that can help you. I believe that SQL-2011 standard temporal features are not too abstract for PostgreSQL to be implemented as a core feature. They have only two temporal periods: application period (which is controlled by application/user) and system time (which is controlled by system/database, but you cannot specify *how* the system control it), they does not use a special type for storing periods (which is unefficient), they are tied to DATE/TIMESTAMP types (what if you need to store revision numbers instead of time?) Regards, Miro 2012/12/25 Vlad Arkhipov arhi...@dc.baikal.ru mailto:arhi...@dc.baikal.ru Hi all, Currently I'm working on a large enterprise project that heavily uses temporal features. We are using PostgreSQL database for data storage. Now we are using PL/pgSQL trigger-based and application-based solutions to handle with temporal data. However we would like to see this functionality in PostgreSQL core, especially in SQL 2011 syntax. There were some discussions several months ago on temporal support and audit logs: http://archives.postgresql.org/pgsql-hackers/2012-05/msg00765.php http://archives.postgresql.org/pgsql-hackers/2012-08/msg00680.php But currently it seems that there is no active work in this area (am I wrong?) Now I'm rewriting our temporal solutions into an extension that is based on C-language triggers to get a better sense of the problem space and various use cases. There are two aspects that temporal features usually include: system-time (aka transaction-time) and application-time (aka valid-time or business-time). The topics above discussed only the first one. However there is also another one, which includes application-time periods, partial updated/deletes queries, querying for a portion of application time etc. Details can be found here http://metadata-standards.org/Document-library/Documents-by-number/WG2-N1501-N1550/WG2_N1536_koa046-Temporal-features-in-SQL-standard.pdf or in the SQL-2011 Standard Draft which is available freely on the network. It's hard to create a convenient extension for application-time periods because it needs the parser to be changed (however an extension may be useful for referential integrity checks for application-time period temporal tables). I created a simple solution for system-time period temporal tables, that consist of only one trigger (it resembles SPI/timetravel trigger but is based on new range types that were introduced in PostgreSQL 9.2 and it's closer to the SQL-2011 approach for implementation of temporal features). http://pgxn.org/dist/temporal_tables/1.0.0/ I'm not a PostgreSQL expert, so I would appreciate if someone could review the code briefly. There are some places I'm not sure I use some functions properly. Also there are some slight problems with the design that I would like to discuss if anyone is interested in. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org mailto:pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Get current query in a trigger function
On 01/15/2013 01:45 AM, Robert Haas wrote: On Fri, Jan 11, 2013 at 4:47 AM, Vlad Arkhipov arhi...@dc.baikal.ru wrote: Is there any simple way of getting a query for which a trigger was executed? debug_query_string and ActivePortal-sourceText return the top query when there are nested triggers. I believe - only if the trigger is written in C. Yes, the trigger is written in C. But I didn't find any way to get access to the current EState or QueryDesc from a trigger function. The only common place of a trigger and the corresponding EState/QueryDesc structs seems to be CurrentMemoryContext in a trigger function, which ancestor has to be (?) EState-es_query_cxt. It's an ugly solution of course. P.S. Is it a mistype EState-es_query_cxt? Should it be es_query_ctx? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Get current query in a trigger function
Is there any simple way of getting a query for which a trigger was executed? debug_query_string and ActivePortal-sourceText return the top query when there are nested triggers. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Rewriter hook
Hi all, Are there any plans on adding a rewriter hook? There are already exist parser, planner, executor hooks but there is no way to control rewriter from plugins. Some use cases: 1. Complex rules in C language. 2. Transforming an original query into a series of queries. For example, instead of UPDATE query on a table you may wish to execute UPDATE and INSERT into *the same* table. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rewriter hook
On 12/29/2012 11:05 AM, Jaime Casanova wrote:
On Fri, Dec 28, 2012 at 8:36 PM, Vlad Arkhipov arhi...@dc.baikal.ru wrote:
Some use cases:
1. Complex rules in C language.
2. Transforming an original query into a series of queries. For example,
instead of UPDATE query on a table you may wish to execute UPDATE and INSERT
into *the same* table.
the second one you can do it with a trigger, and i'm pretty sure you
can use triggers to solve most of the problems... what are you trying
to do?
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157
I'm trying to make an extension that rewrites UPDATE/DELETE queries to
specific tables using some additional information via special functions
into a query (in order to not extend the parser with special syntax).
For example, a query
UPDATE my_table
SET val = 2
WHERE id = 1
AND special_func(daterange('2000-02-01', '2000-03-01'));
needs to be rewritten into the following three queries:
UPDATE my_table SET val = 2
WHERE id = 1
AND period @ daterange('2000-02-01', '2000-03-01');
UPDATE my_table SET period = period - daterange('2000-02-01', '2000-03-01')
WHERE id = 1
AND period daterange('2000-02-01', '2000-03-01')
AND (period daterange('2000-02-01', '2000-03-01')
OR period daterange('2000-02-01', '2000-03-01'));
INSERT INTO my_table (id, val, period)
SELECT 1, 2, period * daterange('2000-02-01', '2000-03-01')
FROM my_table
WHERE id = 1
AND period daterange('2000-02-01', '2000-03-01')
AND (NOT period daterange('2000-02-01', '2000-03-01')
OR NOT period daterange('2000-02-01', '2000-03-01'));
Here is the same query with special syntax (SQL-2011 variant):
UPDATE my_table FOR PORTION OF period_name FROM '2000-02-01' TO '2000-03-01'
SET val = 2
WHERE id = 1;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Temporal features in PostgreSQL
Hi all, Currently I'm working on a large enterprise project that heavily uses temporal features. We are using PostgreSQL database for data storage. Now we are using PL/pgSQL trigger-based and application-based solutions to handle with temporal data. However we would like to see this functionality in PostgreSQL core, especially in SQL 2011 syntax. There were some discussions several months ago on temporal support and audit logs: http://archives.postgresql.org/pgsql-hackers/2012-05/msg00765.php http://archives.postgresql.org/pgsql-hackers/2012-08/msg00680.php But currently it seems that there is no active work in this area (am I wrong?) Now I'm rewriting our temporal solutions into an extension that is based on C-language triggers to get a better sense of the problem space and various use cases. There are two aspects that temporal features usually include: system-time (aka transaction-time) and application-time (aka valid-time or business-time). The topics above discussed only the first one. However there is also another one, which includes application-time periods, partial updated/deletes queries, querying for a portion of application time etc. Details can be found here http://metadata-standards.org/Document-library/Documents-by-number/WG2-N1501-N1550/WG2_N1536_koa046-Temporal-features-in-SQL-standard.pdf or in the SQL-2011 Standard Draft which is available freely on the network. It's hard to create a convenient extension for application-time periods because it needs the parser to be changed (however an extension may be useful for referential integrity checks for application-time period temporal tables). I created a simple solution for system-time period temporal tables, that consist of only one trigger (it resembles SPI/timetravel trigger but is based on new range types that were introduced in PostgreSQL 9.2 and it's closer to the SQL-2011 approach for implementation of temporal features). http://pgxn.org/dist/temporal_tables/1.0.0/ I'm not a PostgreSQL expert, so I would appreciate if someone could review the code briefly. There are some places I'm not sure I use some functions properly. Also there are some slight problems with the design that I would like to discuss if anyone is interested in. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] How to check whether the row was modified by this transaction before?
In a BEFORE UPDATE trigger I need to know whether the row was previously modified by this transaction. Is it safe to use xmin and txid_current() for this purpose (xmin is 32-bit txid type but txid_current() returns 64-bit bigint). CREATE FUNCTION test_trigger() RETURNS TRIGGER AS $$ BEGIN IF OLD.xmin = txid_current() THEN -- Do something. ELSE -- Do something else. END IF; END; $$ LANGUAGE plpgsql; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to check whether the row was modified by this transaction before?
On 12/07/2012 02:53 AM, Tom Lane wrote: Vlad Arkhipov arhi...@dc.baikal.ru writes: In a BEFORE UPDATE trigger I need to know whether the row was previously modified by this transaction. Is it safe to use xmin and txid_current() for this purpose (xmin is 32-bit txid type but txid_current() returns 64-bit bigint). IF OLD.xmin = txid_current() THEN Comparing to txid_current() mod 2^32 would probably work, but note this will not think that subtransactions or parent transactions are this transaction, so any use of savepoints or plpgsql exception blocks is likely to cause headaches. Why do you think you need to know this? regards, tom lane The use case is quite simple. I'm trying to rewrite our internal system versioning extension (SQL feature T180) in more abstract way. Any temporal versioned table uses its associated history table to store updated and deleted data rows. For this purpose the extension adds AFTER UPDATE/DELETE triggers to the table that insert OLD row in the history table for updated and deleted rows. But if there are multiple changes to a row in the same transaction the trigger should generate a history row only for the first change. On 12/07/2012 06:26 AM, Tom Lane wrote: It strikes me that the notion of this row was previously modified by the current transaction is squishier than it might look, and we'd do well to clarify it before we consider exporting anything. I think there are three ways you might define such a function: 1. xmin is exactly equal to current (sub)transaction's XID. 2. xmin is this (sub)transaction's XID, or the XID of any subcommitted subtransaction of it. 3. xmin is this (sub)transaction's XID, or the XID of any subcommitted subtransaction, or the XID of any open parent transaction or subcommitted subtransaction thereof. If I understand you correctly, what I'm looking for is described by the 3rd case and I may use TransactionIdIsCurrentTransactionId() for this purpose? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Audit Logs WAS: temporal support patch
On 08/22/2012 08:34 AM, Gavin Flower wrote: About 10 years ago, I implemented some temporal features in a database to cope with insurance quotes that had to be valid for a specified number of days in the future that was invariant with respect to future changes in premiums with effective dates within the period of validity of the quote. If anyone is interested, I'll see if I can find my notes and write it up (but in a different thread!). Cheers, Gavin What you mean is not an audit logs, it's a business time. Pavel Stehule in the beginning of this thread gave a link to a description of SQL2011 design of this feature. Audit logs are more related to system time. For example IBM DB2 uses following syntax for system time (which is mostly SQL2011-conformant). CREATE TABLE policy ( id INT primary key not null, vin VARCHAR(10), annual_mileage INT, rental_car CHAR(1), coverage_amt INT, sys_start TIMESTAMP(12) GENERATED ALWAYS AS ROW BEGIN NOT NULL, sys_end TIMESTAMP(12) GENERATED ALWAYS AS ROW END NOT NULL, trans_start TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID IMPLICITLY HIDDEN, PERIOD SYSTEM_TIME (sys_start, sys_end) ); CREATE TABLE policy_history LIKE policy; ALTER TABLE policy ADD VERSIONING USE HISTORY TABLE policy_history; And the following syntax for querying for historical data. SELECT coverage_amt FROM policy FOR SYSTEM_TIME AS OF '2010-12-01' WHERE id = ; SELECT count(*) FROM policy FOR SYSTEM_TIME FROM '2011-11-30' TO '-12-30' WHERE vin = 'A';
Re: [HACKERS] temporal support patch
On 08/21/2012 01:52 PM, Jeff Davis wrote: On Mon, 2012-08-20 at 16:32 -0700, Josh Berkus wrote: Personally, I would prefer a tool which just made it simpler to build my own triggers, and made it automatic for the history table to track changes in the live table. I think anything we build which controls what goes into the history table, etc., will only narrow the user base. That sounds like a good way to start. Actually, even before the tool, how about just some really good examples of triggers for specific kinds of audit logs, and some ways to run queries on them? I think that might settle a lot of these details. Here is the example of triggers we use in our applications. This is the test implementation, the production one uses similar triggers written in C. http://softus.org/?page_id=63 1. There are 3 tables: test contains only current data, test_history contains only historical data and test_audit contains all data. 2. There must be a field in an audited table system_time for a period of validity of the row. 3. Optional fields are: txid_modified, user_modified for txid/user that inserts or updated the row, txid_deleted, user_deleted for txid/user that deleted the row. There may be other information in the audit table that was omitted in the example (client IP, host name, etc.) 3. We do not use txid_current() as transaction ID because backup/restore resets it. 4. User is set by the application (audit.current_user() is just a dummy). 5. There is no exclusion constraint on (primary key, system_time) in history table, integrity is maintained by triggers (however the user can damage the historical data by modifying test_history table). 6. It's important to understand that when audit triggers are enabled some modifications can fail because the same row may be concurrently modified by another transaction CURRENT_TIMESTAMP of which is lower or the same as the current one.
Re: [HACKERS] temporal support patch
On 05/31/2012 11:52 AM, Jeff Davis wrote: On Wed, 2012-05-16 at 23:14 +0200, Miroslav Šimulčík wrote: Hi all, as a part of my master's thesis I have created temporal support patch for PostgreSQL. It enables the creation of special temporal tables with entries versioning. Modifying operations (UPDATE, DELETE, TRUNCATE) on these tables don't cause permanent changes to entries, but create new versions of them. Thus user can easily get to the past states of the table. I would be very interested to see this, thank you for working on it. There are quite a few aspects to a temporal database system, and you are working on a system-maintained transaction-time historical table, right? Or are there other aspects to your proposal? Some general comments: * I'd very much like to see you make use of Range Types from 9.2; in particular, TSTZRANGE would be much better than holding two timestamps. If a standard requires you to display two timestamps in certain situations, perhaps you could use ranges internally and display the boundaries as timestamps when needed. It's not sufficient to store only a period of validity for a row. If two transactions started in the same time change the same record, you have a problem with TSTZRANGE type because it's normalized to empty interval. The other issue is how to handle multiple changes of the same record within the transaction. Should they be stored or not? Also it's necessary to store some kind of operation type that was applied to the record (insert/update/delete). For example, there is a table with one record with validity period [0, ) and value 'A'. First way 1. Delete this record in time 1, now there is [0, 1), A in the history table. 2. Insert a new record in time 1, now there is [0, 1), A in the history table and [1, ), B record in the current data table. Second way 1. Update this record in time 1, now there is [0, 1), A in the history table and [1, ), B record in the current data table. So you have the same data in the tables but the actions that led to this configuration were different and the history has been lost partly. * There is other useful information that could be recorded, such as the user who inserted/updated/deleted the record. I'm not sure that the database user is the proper thing to be stored in the history table. Many applications usually connect to a database using some virtual user and have their own users/roles tables to handle with privileges. There should be some way to substitute the stored user in the history table with the application's one. It's also helpful to store transaction id that inserted/updated/deleted the record. * For some purposes, it's very useful to keep track of the columns that changed. For instance, a query like show me any time a salary was changed over the last month (or some other rare event) would be very slow to run if there was not some explicit annotation on the historical records (e.g. a columns changed bitmap or something). It's a great proposal but seems to be impossible to implement with triggers only solution, isn't it? Is there any kind of hooks on ALTER TABLE ... in PostgreSQL to update changed columns bitmaps when table structure changes? * In general, I'm not fond of adorning queries with TRANSACTION TIME AS OF... kinds of things. Those constructs are redundant with a WHERE clause (on a range type, you'd use the contains operator). If a standard requires that, maybe it would be OK to allow such things as syntactic sugar. In SQL2011 there is only one table with the all data, historical and current. So it's not very convenient to specifiy WHERE condition on system time everywhere and for all tables in the query. By default only the current data is selected with a query like SELECT * FROM table. * As Jim mentioned, it might make sense to use something resembling inheritance so that selecting from the historical table includes the current data (but with no upper bound for the range). We have a success experience with inheritance with our trigger-based solution. It's completely transparent for the existing applications and does not have any impact on performance. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] temporal support patch
On 06/15/2012 03:59 PM, Jeff Davis wrote: On Wed, 2012-06-13 at 23:10 +0200, Miroslav Šimulčík wrote: I have working patch for postgresql version 9.0.4, but it needs refactoring before i can submit it, because some parts don't meet formatting requirements yet. And yes, changes are large, so it will be better to discuss design first and then deal with code. Do you insist on compatibility with standard SQL 2011 as Pavel wrote? Try to work on solving the problem and identify the use cases. I don't think the standard will cause a major problem, we should be able to make the relevant parts of your patch match the standard. That's one reason to work on it as an extension first: we can get a better sense of the problem space and various use cases without worrying about violating any standard. Then, as you need specific backend support (e.g. special syntax), we can take the standards more seriously. Regards, Jeff Davis What's wrong with SPI/timetravel extension for system versioning? http://www.postgresql.org/docs/9.1/static/contrib-spi.html We are heavily using system-versioned and application-time period tables in our enterprise products (most of them are bi-temporal). However our implementation is based on triggers and views and therefore is not very convenient to use. There are also some locking issues with foreign keys to application-time period tables. It will be great if the new temporal SQL features will be included in the Postgresql core with SQL 2011 syntax support. It is especially important for bi-temporal tables because of complex internal logic of UPDATE/DELETE and huge SELECT queries for such tables.
[HACKERS] COMMENT on function's arguments
Does it make sense to have a comment on function's arguments? Of course it is possible to include these comments in a function's comment, but may be better to have them in more formalized way like comments on columns of a table. IDEs may use this information when providing hints for a function like in other languages. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Weird behaviour
Could anyone please explain the behaviour of Postgres in the cases below? It evaluates an unused expression t.x || t.y in the first case but doesn't do it in the second one. It's also strange that the last explain throws an error. postgres=# select version(); version --- PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit (1 row) postgres=# create or replace function f() postgres-# returns text as $$ postgres$# begin postgres$# raise exception 'here'; postgres$# end; postgres$# $$ language plpgsql immutable; postgres=# select t.x postgres-# from ( postgres(# select t.x, t.x || f() postgres(# from (values(1)) as t(x) postgres(# ) t; ERROR: here postgres=# select t.x postgres-# from ( postgres(# select t.x, t.x::text || f() postgres(# from (values(1)) as t(x) postgres(# ) t; x --- 1 (1 row) postgres=# explain select t.x postgres-# from ( postgres(# select t.x, t.x || f() postgres(# from (values(1)) as t(x) postgres(# ) t; ERROR: here -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Shared sequence-like objects in PostgreSQL
Hello all, I'm writing a C-language function that is similar to nextval() but should return the next member of the recurrent sequence: T(n+1) = f(T(n), T(n-1), ..., T(n-k)), where f is some function and k is a constant. The state of this object should be persistent between database restarts and should be easily recovered if the database crashes. So the first problem I encountered was where to store the current state of this object (n and values T(n), T(n-1), ... T(n-k)). I believe that TopMemoryContext is not shared between processes, therefore I must use shmem functions from backend/storage/ipc/shmem.c to create a structure in shared memory. The next issue is how to synchronize backends' reads/writes to this chunk of shared memory. I suppose there must be something to handle with semaphores in the Postgres code. Then I periodically need to persist the state of this object to the database, for example for every 100 generated values, as well as on the postmaster's shutdown. What is the best method for doing that? Please let me know if this problem has been solved before. Thanks for you help. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] date_part for infinity intervals
The behaviour of date_part function is opaque for infinity intervals.
For example
date_part('epoch', 'infinity'::date) and date_part('year',
'infinity'::date) return zero but is supposed to return 'infinity',
date_part('day', 'infinity'::date) returns zero, should it return 'NaN'
instead?
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Predicate locking
30.04.2011 22:18, Kevin Grittner wrote: Vlad Arkhipov wrote: 29.04.2011 21:18, Kevin Grittner wrote: Vlad Arkhipov wrote: But even if it would work it would not help me anyways. Because my constraint is much more complex and depends on other tables, I cannot express it in terms of exclusion constraints. Are you aware of the changes to the SERIALIZABLE transaction isolation level in the upcoming 9.1 release? http://wiki.postgresql.org/wiki/Serializable http://wiki.postgresql.org/wiki/SSI If you can wait for that, it might be just what you're looking for. I would not like to make the whole transaction serializable because of performance and concurrency reasons. I'm curious -- what do you expect the performance and concurrency impact to be? You do realize that unlike SELECT FOR UPDATE, SERIALIZABLE in PostgreSQL 9.1 will not cause any blocking beyond what is there in READ COMMITTED, right? Does 9.1beta contain the new SERIALIZABLE isolation level? If so, I can show you some concurrency issues. First I created a table: create table t (id bigint, value bigint); insert into t values (1, 1); insert into t values (2, 1); create index t_idx on t(id); Then I started two transactions. 1. begin transaction; set transaction isolation level serializable; select * from t where id = 2; // and do some logic depending on this result insert into t (id, value) values (-2, 1); 2. begin transaction; set transaction isolation level serializable; select * from t where id = 3; // and do some logic depending on this result insert into t (id, value) values (-3, 0); Then I commited the both and the second one raised an exception: ERROR: could not serialize access due to read/write dependencies among transactions SQL state: 40001 However the second transaction does not access the records that the first one does. If I had predicate locks I could avoid this situation by locking the records with the specified id. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Predicate locking
29.04.2011 21:18, Kevin Grittner wrote: Vlad Arkhipov wrote: But even if it would work it would not help me anyways. Because my constraint is much more complex and depends on other tables, I cannot express it in terms of exclusion constraints. Are you aware of the changes to the SERIALIZABLE transaction isolation level in the upcoming 9.1 release? http://wiki.postgresql.org/wiki/Serializable http://wiki.postgresql.org/wiki/SSI If you can wait for that, it might be just what you're looking for. -Kevin I would not like to make the whole transaction serializable because of performance and concurrency reasons. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Predicate locking
28.04.2011 21:36, David Fetter пишет: On Thu, Apr 28, 2011 at 12:07:34PM +0900, Vlad Arkhipov wrote: 27.04.2011 18:38, Heikki Linnakangas пишет: On 27.04.2011 12:24, Vlad Arkhipov wrote: 27.04.2011 17:45, Nicolas Barbier: 2011/4/27 Vlad Arkhipovarhi...@dc.baikal.ru: I'm currently need predicate locking in the project, so there are two ways to get it by now: implement it by creating special database records to lock with SELECT FOR UPDATE or wait while they will be implemented in Postgres core. Is there something like predicate locking on the TODO list currently? I assume you want (real, as opposed to what is in 9.1 now) SERIALIZABLE transactions, in which case you could check: URL:http://wiki.postgresql.org/wiki/Serializable Nicolas Not sure about the whole transaction, I think it degrades the performance too much as transactions access many tables. Just wanted SELECT FOR UPDATE to prevent inserting records into a table with the specified condition. It seems to be very typical situation when you have a table like CREATE TABLE timetable (start_ts TIMESTAMP, end_ts TIMESTAMP) and before insertion in this table want to guarantee that there is no overlapped time intervals there. So, first you need to lock the range in the table, then to check if there are any records in this range. In my case this table is the only for which I need such kind of locking. You can do that with exclusion constraints: http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION) See also Depesz's blog post for a specific example on how to use it for time ranges: http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/ And Jeff Davis's blog post that uses the period data type instead of the hack to represent time ranges as boxes: http://thoughts.j-davis.com/2009/11/08/temporal-keys-part-2/ Exclusion constraints works only in simple cases. I need to check a great amount of business rules to assure that the insertion is possible. For example, for a table with columns (start_ts TIMESTAMP, end_ts TIMESTAMP, room BIGINT, visitor BIGINT, service BIGINT) it's not possible to have overlapped intervals for the same time and room, but different visitors. So, in terms of exclusion constraints I need something like: room WITH =, visitor WITH, (start_ts, end_ts) WITH which seems to be impossible. Predicate locking provides more flexible way to solve this problem. Did you actually try it? It works just fine with a timestamp range. Cheers, David. Yes. It does not work on 9.0 when I add 'visitor WITH '. ERROR: failed to re-find tuple within index overlapping HINT: This may be because of a non-immutable index expression. But even if it would work it would not help me anyways. Because my constraint is much more complex and depends on other tables, I cannot express it in terms of exclusion constraints. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Predicate locking
I'm currently need predicate locking in the project, so there are two ways to get it by now: implement it by creating special database records to lock with SELECT FOR UPDATE or wait while they will be implemented in Postgres core. Is there something like predicate locking on the TODO list currently? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Predicate locking
27.04.2011 17:45, Nicolas Barbier: 2011/4/27 Vlad Arkhipovarhi...@dc.baikal.ru: I'm currently need predicate locking in the project, so there are two ways to get it by now: implement it by creating special database records to lock with SELECT FOR UPDATE or wait while they will be implemented in Postgres core. Is there something like predicate locking on the TODO list currently? I assume you want (real, as opposed to what is in 9.1 now) SERIALIZABLE transactions, in which case you could check: URL:http://wiki.postgresql.org/wiki/Serializable Nicolas Not sure about the whole transaction, I think it degrades the performance too much as transactions access many tables. Just wanted SELECT FOR UPDATE to prevent inserting records into a table with the specified condition. It seems to be very typical situation when you have a table like CREATE TABLE timetable (start_ts TIMESTAMP, end_ts TIMESTAMP) and before insertion in this table want to guarantee that there is no overlapped time intervals there. So, first you need to lock the range in the table, then to check if there are any records in this range. In my case this table is the only for which I need such kind of locking. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Predicate locking
27.04.2011 18:38, Heikki Linnakangas пишет: On 27.04.2011 12:24, Vlad Arkhipov wrote: 27.04.2011 17:45, Nicolas Barbier: 2011/4/27 Vlad Arkhipovarhi...@dc.baikal.ru: I'm currently need predicate locking in the project, so there are two ways to get it by now: implement it by creating special database records to lock with SELECT FOR UPDATE or wait while they will be implemented in Postgres core. Is there something like predicate locking on the TODO list currently? I assume you want (real, as opposed to what is in 9.1 now) SERIALIZABLE transactions, in which case you could check: URL:http://wiki.postgresql.org/wiki/Serializable Nicolas Not sure about the whole transaction, I think it degrades the performance too much as transactions access many tables. Just wanted SELECT FOR UPDATE to prevent inserting records into a table with the specified condition. It seems to be very typical situation when you have a table like CREATE TABLE timetable (start_ts TIMESTAMP, end_ts TIMESTAMP) and before insertion in this table want to guarantee that there is no overlapped time intervals there. So, first you need to lock the range in the table, then to check if there are any records in this range. In my case this table is the only for which I need such kind of locking. You can do that with exclusion constraints: http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION) See also Depesz's blog post for a specific example on how to use it for time ranges: http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/ And Jeff Davis's blog post that uses the period data type instead of the hack to represent time ranges as boxes: http://thoughts.j-davis.com/2009/11/08/temporal-keys-part-2/ Exclusion constraints works only in simple cases. I need to check a great amount of business rules to assure that the insertion is possible. For example, for a table with columns (start_ts TIMESTAMP, end_ts TIMESTAMP, room BIGINT, visitor BIGINT, service BIGINT) it's not possible to have overlapped intervals for the same time and room, but different visitors. So, in terms of exclusion constraints I need something like: room WITH =, visitor WITH , (start_ts, end_ts) WITH which seems to be impossible. Predicate locking provides more flexible way to solve this problem. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Reading from a REFCURSOR in a C language function
Hi, I'm trying to write a C language function that has a REFCURSOR argument. Could anyone please give me an example of reading from a cursor in C code? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reading from a REFCURSOR in a C language function
06.04.2011 02:06, Jan Wieck wrote: On 4/5/2011 3:24 AM, Vlad Arkhipov wrote: Hi, I'm trying to write a C language function that has a REFCURSOR argument. Could anyone please give me an example of reading from a cursor in C code? Sorry, I don't have a code example. A refcursor data type is basically a string, containing the name of an open cursor (portal). It is stored binary compatible to the text data type. In the C function, you extract that name (using the textout function) and use it inside the FETCH query as the cursor name. You may need to double-quote that string. Jan Thanks for the hint. It works great. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] What happens If a table changes during a query/procedure execution
Let there are two transactions that were created with read commited isolation level. In the first one we're executing a SELECT query: SELECT * FROM t UNION ALL SELECT * FROM t; In the second transaction we're modifying the same table: INSERT INTO t DEFAULT VALUES; COMMIT; Is it possible that the last UNION part in the first query will retrieve not the same rows as the first one? Another scenario is where we're executing two SELECT queries in a stored procedure: BEGIN ... SELECT * FROM t; SELECT * FROM t; END; Is it possible to get different results in the second query? Does SQL standard define the behaviour in such cases? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What happens If a table changes during a query/procedure execution
09.03.2011 18:54, Nicolas Barbier: 2011/3/9 Vlad Arkhipovarhi...@dc.baikal.ru: Let there are two transactions that were created with read commited isolation level. In the first one we're executing a SELECT query: SELECT * FROM t UNION ALL SELECT * FROM t; In the second transaction we're modifying the same table: INSERT INTO t DEFAULT VALUES; COMMIT; Is it possible that the last UNION part in the first query will retrieve not the same rows as the first one? No, because statements never see changes made by other transactions while they are in flight. Is it also true if a statement contains subqueries or function calls? For instance, CREATE FUNCTION f() RETURNS NUMERIC AS $$ BEGIN RETURN (SELECT SUM(a) FROM t); END; $$ LANGUAGE 'plpgsql'; SELECT a, f() FROM t; or SELECT a, (SELECT SUM(a) FROM t) FROM t; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Name column
I have just come across a weird thing. It works for any table and seems to be not documented. SELECT c.name FROM (VALUES(1, 'A', true)) c; SELECT c.name FROM pg_class c; And it does not work in these cases: SELECT name FROM (VALUES(1, 'A', true)); SELECT name FROM pg_class; PostgreSQL 8.4.2 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42), 64-bit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Duplicate key value error
Is it possible to print which key value is duplicated when 'Duplicate key value violates unique constraint' occurs? Foreign key violation error reports such kind of information. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] DDL+SQL in PL/pgSQL EXECUTE
Is it a bug or by design? I could not find what behaviour is correct for
these statements in PL/pgSQL:
This function just executes a string.
CREATE OR REPLACE FUNCTION _EXEC(query VARCHAR)
RETURNS VOID AS $$
BEGIN
EXECUTE query;
END;
$$ LANGUAGE 'plpgsql';
1. Works ok.
BEGIN WORK;
SELECT _EXEC('CREATE TABLE T(ID INTEGER); CREATE INDEX T_IDX ON T(ID)');
ROLLBACK;
2. Works ok.
BEGIN WORK;
SELECT _EXEC('CREATE TABLE T(ID INTEGER); ALTER TABLE T ADD COLUMN ID2
INTEGER; CREATE INDEX T_IDX2 ON T(ID2)');
ROLLBACK;
3. ERROR: relation t does not exist
SELECT _EXEC('CREATE TABLE T(ID INTEGER); INSERT INTO T(ID) VALUES (1)');
4. Inserts NULL value into ID column instead of default 10.
BEGIN WORK;
CREATE TABLE T(ID INTEGER);
SELECT _EXEC('ALTER TABLE T ALTER COLUMN ID SET DEFAULT(10); INSERT INTO
T DEFAULT VALUES');
SELECT * FROM T;
-
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] View running statements
In response to Vlad Arkhipov : Is there any way to inspect current running statements (optionally full stack of these statements)? I've found there is error_context_stack variable in each backend, but it seems there is no way to get this variable from another backend. It will be great if Postgres have such kind of mechanism, for instance a system table that consists statements running longer than certain time (5-10 seconds). select * from pg_stat_activity; Andreas It shows only top-level statements. You can see 'select * from f()' in this table but you never see what is actually executing in this function. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] View running statements
Is there any way to inspect current running statements (optionally full stack of these statements)? I've found there is error_context_stack variable in each backend, but it seems there is no way to get this variable from another backend. It will be great if Postgres have such kind of mechanism, for instance a system table that consists statements running longer than certain time (5-10 seconds). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
