[HACKERS] Values of fields in Rules
Hi, when i create rule(on insert) on view(select id, name from users), i will recieve values that were inserted in form of reference words new.id, new.name. if i insert into users (id, name) values (null, null); then new.id = null and new.name = null if i insert into users (name) values (null); then new.id = null and new.name = null is there any way how to distinguish that id column wasnt explicitly named? -- 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] Values of fields in Rules
mito wrote: Hi, when i create rule(on insert) on view(select id, name from users), i will recieve values that were inserted in form of reference words new.id, new.name. if i insert into users (id, name) values (null, null); then new.id = null and new.name = null if i insert into users (name) values (null); then new.id = null and new.name = null is there any way how to distinguish that id column wasnt explicitly named? is there any way how to determinate in rule that null comes from explicit insert or from not naming column in insert statment -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Recursive plpgsql function in rule
I am trying to select subtree by parent id in table: create table categories( id int, parent_id int ); I am fetching it by recursive plpgsql function, which works well. When i use that function as part of rule operation, i am getting ERROR: stack depth limit exceeded. I have tried to rewrite without recursion which cause infinite loop in rule. Is it possible to sent dynamic structures in planer? Or how to fetch subtree without change of table structure inside rule? Thanks -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Reference on partial data
Hi, is it possible to create reference constraint only on defined subset of data? Reference can be defined on column which has unique constraint. It is also possible to create partial unique index. So it should by possible to reference this column. But postgres can not see this constraint and raises: ERROR: there is no unique constraint matching given keys for referenced table Any ideas ??? I dont feel my self to rewrite builtin trigger function which ensures reference integrity. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Postgres SQL specification (tests)
Hi, i have built an SQL interface using rule system which supports row versioning and i would like to test it against Postgres SQL specification. Is there something like test cases for postgres SQL interface? Or do you have any ideas how to build a group of all possible cases of table structure and generate appropriate queries, which can be tested against postgres? thanks -- 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] Postgres SQL specification (tests)
By table structure i mean table definition options. Kevin Field wrote: On Apr 16, 3:14 am, mito milos.ors...@gmail.com wrote: Hi, i have built an SQL interface using rule system which supports row versioning and i would like to test it against Postgres SQL specification. Is there something like test cases for postgres SQL interface? Or do you have any ideas how to build a group of all possible cases of table structure and generate appropriate queries, which can be tested against postgres? thanks For unit testing, I use pgTap, it's pretty handy for scripting your tests. What do you mean by all possible cases of table structure? That would be infinite... -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Affected rows count by rule as condtition
Hi, is there any way how to count affected rows by on update rule and use it as part of condtions. Example: CREATE OR REPLACE RULE _UPDATE AS ON UPDATE TO users DO INSTEAD ( UPDATE s_users SET id = new.id, login = new.login, WHERE id IN (SELECT id FROM s_users ) AND 2 (SELECT count(new.id)) ; Error: agregate functions not allowed in WHERE statement It need to simulate unique constraint on field s_users.new_id, so it should deny to update multiple rows with same value. Any suggestions are welcome. -- 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] Affected rows count by rule as condtition
I am using rules as layer to save every version of row in shadow table, so i cant use unique constraint on column, because of many versions may have same value. mito Jaime Casanova wrote: On Mon, Apr 13, 2009 at 11:12 AM, mito milos.ors...@gmail.com wrote: It need to simulate unique constraint on field s_users.new_id, so it should deny to update multiple rows with same value. Any suggestions are welcome. why not simply create a UNIQUE constraint? -- 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] Affected rows count by rule as condtition
This will deny insert of value that allready exists. Which is ok. But the second scenerio in which unique constraint refuse operation is, when u try to update more rows to same value in column with unique constraint. So i need to use count of affected rows, to deny operation if there are more then one. I am using rules as layer to save every version of row in shadow table, so i cant use unique constraint on column, because of many versions may have same value. Robert Haas wrote: On Mon, Apr 13, 2009 at 12:12 PM, mito milos.ors...@gmail.com wrote: Hi, is there any way how to count affected rows by on update rule and use it as part of condtions. Example: CREATE OR REPLACE RULE _UPDATE AS ON UPDATE TO users DO INSTEAD ( UPDATE s_users SET id = new.id, login = new.login, WHERE id IN (SELECT id FROM s_users ) AND 2 (SELECT count(new.id)) ; Error: agregate functions not allowed in WHERE statement It need to simulate unique constraint on field s_users.new_id, so it should deny to update multiple rows with same value. Any suggestions are welcome. Well, you could probably make this compile by rewriting the broken part as SELECT SUM(1) FROM s_users WHERE id = NEW.id, but it won't guarantee uniqueness in the face of concurrent transactions, even if you use SERIALIZABLE mode. There's a reason that unique constraints are built into the database you should use them. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WAL files format
Hi, iam developing version control system based on content because i havent found anything for postgres and it sounds like great tool to have. Propably i will use Rules and Views with shadow tables, but iam interested in alternative to examine WAL files. So iam interested in WAL file format and information that can be extracted from it. Is there any way to build some API above WAL file to be able to analyze data changes in time caused by DML ? Iam aware of xlog.c but not of its capabilities and features, its kind of hard for me to analyze that file. MS SQL create transaction logs and there are tools like ApexSQL Log that can analyze logs a redo changes made by DML. So i thought it would by great to have such tools for opensource system like postgres. Also, if anybody has any ideas or alternatives on content version control system i will be realy thankfull. Thanks Milos -- 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] WAL files format
What information are currently strored in WAL sequence ? Jonah H. Harris wrote: xlog.c :) On Sun, Apr 20, 2008 at 1:55 PM, Miloš Ország [EMAIL PROTECTED] wrote: Is there any document describing WAL file format? On Sun, Apr 20, 2008 at 7:20 PM, Jonah H. Harris [EMAIL PROTECTED] wrote: See xlogdump and xlogviewer. On Sun, Apr 20, 2008 at 7:44 AM, mito [EMAIL PROTECTED] wrote: Hi, iam developing version control system based on content because i havent found anything for postgres and it sounds like great tool to have. Propably i will use Rules and Views with shadow tables, but iam interested in alternative to examine WAL files. So iam interested in WAL file format and information that can be extracted from it. Is there any way to build some API above WAL file to be able to analyze data changes in time caused by DML ? Iam aware of xlog.c but not of its capabilities and features, its kind of hard for me to analyze that file. MS SQL create transaction logs and there are tools like ApexSQL Log that can analyze logs a redo changes made by DML. So i thought it would by great to have such tools for opensource system like postgres. Also, if anybody has any ideas or alternatives on content version control system i will be realy thankfull. Thanks Milos -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837 | http://www.enterprisedb.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
