[HACKERS] View running statements

2009-03-11 Thread 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

Re: [HACKERS] View running statements

2009-03-12 Thread Vlad Arkhipov
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

[HACKERS] DDL+SQL in PL/pgSQL EXECUTE

2009-03-18 Thread Vlad Arkhipov
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

[HACKERS] Duplicate key value error

2009-03-31 Thread Vlad Arkhipov
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:

[HACKERS] Weird behaviour

2012-03-14 Thread Vlad Arkhipov
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();

[HACKERS] How to check whether the row was modified by this transaction before?

2012-12-06 Thread Vlad Arkhipov
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

Re: [HACKERS] How to check whether the row was modified by this transaction before?

2012-12-06 Thread Vlad Arkhipov
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

[HACKERS] Temporal features in PostgreSQL

2012-12-25 Thread Vlad Arkhipov
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

[HACKERS] Rewriter hook

2012-12-28 Thread Vlad Arkhipov
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,

Re: [HACKERS] Rewriter hook

2012-12-28 Thread Vlad Arkhipov
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

[HACKERS] Get current query in a trigger function

2013-01-11 Thread Vlad Arkhipov
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:

Re: [HACKERS] Get current query in a trigger function

2013-01-14 Thread Vlad Arkhipov
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

Re: [HACKERS] temporal support patch

2012-08-21 Thread Vlad Arkhipov
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

Re: [HACKERS] Audit Logs WAS: temporal support patch

2012-08-22 Thread Vlad Arkhipov
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

[HACKERS] COMMENT on function's arguments

2012-06-12 Thread Vlad Arkhipov
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

Re: [HACKERS] temporal support patch

2012-06-18 Thread Vlad Arkhipov
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

Re: [HACKERS] temporal support patch

2012-06-25 Thread Vlad Arkhipov
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

[HACKERS] date_part for infinity intervals

2011-06-20 Thread Vlad Arkhipov
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? --

Re: [HACKERS] Temporal features in PostgreSQL

2013-02-13 Thread Vlad Arkhipov
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

Re: [HACKERS] Temporal features in PostgreSQL

2013-02-17 Thread Vlad Arkhipov
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

[HACKERS] Name column

2010-09-24 Thread Vlad Arkhipov
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

[HACKERS] Shared sequence-like objects in PostgreSQL

2011-09-21 Thread Vlad Arkhipov
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

[HACKERS] What happens If a table changes during a query/procedure execution

2011-03-09 Thread Vlad Arkhipov
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

Re: [HACKERS] What happens If a table changes during a query/procedure execution

2011-03-09 Thread Vlad Arkhipov
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

[HACKERS] Reading from a REFCURSOR in a C language function

2011-04-05 Thread Vlad Arkhipov
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:

Re: [HACKERS] Reading from a REFCURSOR in a C language function

2011-04-05 Thread Vlad Arkhipov
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

[HACKERS] Predicate locking

2011-04-27 Thread Vlad Arkhipov
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

Re: [HACKERS] Predicate locking

2011-04-27 Thread Vlad Arkhipov
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

Re: [HACKERS] Predicate locking

2011-04-27 Thread Vlad Arkhipov
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

Re: [HACKERS] Predicate locking

2011-04-28 Thread Vlad Arkhipov
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

Re: [HACKERS] Predicate locking

2011-04-29 Thread Vlad Arkhipov
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

Re: [HACKERS] Predicate locking

2011-05-02 Thread Vlad Arkhipov
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

[HACKERS] System catalog vacuum issues

2013-08-05 Thread Vlad Arkhipov
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

Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Vlad Arkhipov
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

Re: [HACKERS] System catalog vacuum issues

2013-08-06 Thread Vlad Arkhipov
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

Re: [HACKERS] System catalog vacuum issues

2013-08-13 Thread Vlad Arkhipov
/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

Re: [HACKERS] System catalog vacuum issues

2013-08-15 Thread Vlad Arkhipov
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

Re: [HACKERS] System catalog vacuum issues

2013-08-15 Thread Vlad Arkhipov
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