[GENERAL] Trouble with PQnotifies()
Dear gurus,
CREATE OR REPLACE RULE r1i AS ON INSERT TO t1 DO NOTIFY NotifyMe;
CREATE OR REPLACE RULE r1u AS ON UPDATE TO t1 DO NOTIFY NotifyMe;
CREATE OR REPLACE RULE r1d AS ON DELETE TO t1 DO NOTIFY NotifyMe;
The main thread in my Windows application launches several worker threads.
Several worker threads connect to PostgreSQL by via libpq.dll and execute SQL
requests from clients. Several other worker threads, as illustrated below, also
connect to PostgreSQL via libpq and listen to notifications.
The SQL execution thread calls PG front end functions provided in libpq.dll. On
the other hand, the listening thread calls PG front end functions provided in
libpq.lib which are all statically linked to this application. Using two copies
of PG front end functions codes makes the size of my applciation bloated but I
presume there is no other side effect except the program size.
The following listening worker thread behaves as expected if I insert/delete
rows into/from table "t1" in psql prompt.
My trouble is when the SQL execution worker thread inserts/ deletes rows
into/from table "t1", the listening worker thread then goes crazy: PQnotifies()
always returns NULL which pushes the listening thread to grab all CPU power
because select() returns immediately in every iteration. The weird part is that
select() says that there is something available but PQnotifies() returns NULL.
-
PGconn *c=/* Take one connection from connection pool */;
PGresult *result=PQexec(c,"LISTEN NotifyMe");
PQclear(result);
fd_set InputMask;
int sock=PQsocket(c);
struct timeval TimeOut={1,20};
int SelectResult;
PGnotify *notify;
int terminated=0;
while(!terminated){
FD_ZERO(&InputMask);
FD_SET((unsigned int)sock,&InputMask);
SelectResult=select(sock+1,&InputMask,NULL,NULL,&TimeOut);
if(SelectResult == SOCKET_ERROR){
puts("select() failed:");
break;
}
if(SelectResult == 0)
continue;
if(!FD_ISSET(sock,&InputMask))
continue;
PQconsumeInput(c);
while((notify=PQnotifies(c)) != NULL){ //here: unstable!
if(stricmp(notify->relname,"NotifyMe") == 0)
puts("Got notification");
PQfreemem(notify);
}
}
PQfinish(c);
-
What can be the possible cause of the abnormality?
Best Regards,
CN
--
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 keep the last row of a data set?
I am trying to implement a mechanism that prohibits the last row of a data set from being deleted. CREATE TABLE t1 (c1 INTEGER,c2 INTEGER, PRIMARY KEY (c1,c2)); INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(2,1),(2,2),(2,3); My desired effect: Case 1, Permit this SQL to be executed: DELETE FROM t1 WHERE c1=1 AND c2 <> 2; This SQL keeps one row whose column c1 holds value "1". It does not hurt. Case 2, Raise exception if users attempt to run this SQL: DELETE FROM t1 WHERE c1=1; This SQL attempts to delete all rows having value "1" in column c1. It must be automatically aborted. The following trigger protects nothing: CREATE OR REPLACE FUNCTION tfd() RETURNS TRIGGER AS $$ BEGIN RAISE NOTICE '%',(SELECT COUNT(*) FROM t1 WHERE c1=OLD.c1); IF (SELECT COUNT(*) FROM t1 WHERE c1=OLD.c1) = 1 THEN RAISE EXCEPTION 'Must keep at least 1 row for c1="%"',OLD.c1; END IF; RETURN OLD; END $$ LANGUAGE PLPGSQL STABLE; CREATE TRIGGER td BEFORE DELETE ON t1 FOR EACH ROW EXECUTE PROCEDURE tfd(); postgres@AMD64:/tmp$ psql -c 'DELETE FROM t1' test Notice: 3 Notice: 3 Notice: 3 Notice: 3 Notice: 3 Notice: 3 DELETE 6 postgres@AMD64:/tmp$ Thank you in advance for helping me out! Best Regards, CN -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to keep the last row of a data set?
> -Original Message- > From: Thomas Markus > Sent: Thu, Dec 13 2012 23:14:21 CST > To: seil...@so-net.net.tw > Subject: Re: [GENERAL] How to keep the last row of a data set? > > Hi, > > create an after delete trigger with > > IF (SELECT 1 FROM t1 limit 1) is null THEN > RAISE EXCEPTION 'Must keep at least 1 row'; > > > > hth > Thomas > AFTER DELETE trigger does not prevent all rows from being deleted, either: CREATE TABLE t1 (c1 INTEGER,c2 INTEGER, PRIMARY KEY (c1,c2)); INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(2,1),(2,2),(2,3); CREATE OR REPLACE FUNCTION tfd() RETURNS TRIGGER AS $$ BEGIN RAISE NOTICE '%',(SELECT COUNT(*) FROM t1 WHERE c1=OLD.c1); --IF NOT EXISTS (SELECT 1 FROM t1 WHERE c1=OLD.c1) THEN IF (SELECT 1 FROM t1 WHERE c1=OLD.c1 LIMIT 1) IS NULL THEN RAISE EXCEPTION 'Must keep at least 1 row for c1="%"',OLD.c1; END IF; RETURN OLD; END $$ LANGUAGE PLPGSQL STABLE; CREATE TRIGGER td AFTER DELETE ON t1 FOR EACH ROW EXECUTE PROCEDURE tfd(); test: DELETE FROM t1 WHERE c1=1; Best Regards, CN -- 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] Trouble with PQnotifies()
> The following listening worker thread behaves as expected if I insert/delete
> rows into/from table "t1" in psql prompt.
>
> My trouble is when the SQL execution worker thread inserts/ deletes rows
> into/from table "t1", the listening worker thread then goes crazy:
> PQnotifies() always returns NULL which pushes the listening thread to grab
> all CPU power because select() returns immediately in every iteration. The
> weird part is that select() says that there is something available but
> PQnotifies() returns NULL.
>
> -
> PGconn *c=/* Take one connection from connection pool */;
> PGresult *result=PQexec(c,"LISTEN NotifyMe");
> PQclear(result);
> fd_set InputMask;
> int sock=PQsocket(c);
> struct timeval TimeOut={1,20};
> int SelectResult;
> PGnotify *notify;
> int terminated=0;
> while(!terminated){
> FD_ZERO(&InputMask);
> FD_SET((unsigned int)sock,&InputMask);
> SelectResult=select(sock+1,&InputMask,NULL,NULL,&TimeOut);
> if(SelectResult == SOCKET_ERROR){
> puts("select() failed:");
> break;
> }
> if(SelectResult == 0)
> continue;
> if(!FD_ISSET(sock,&InputMask))
> continue;
> PQconsumeInput(c);
> while((notify=PQnotifies(c)) != NULL){ //here: unstable!
> if(stricmp(notify->relname,"NotifyMe") == 0)
> puts("Got notification");
> PQfreemem(notify);
> }
> }
> PQfinish(c);
Please ignore this question!
My connection pool implementation seems to have flaw. Somehow and somewhere the
connection acquired by a thread is robbed by other threads. The PGconn sending
"LISTEN NotifyMe" becomes different from the PGconn passed to PQsocket(),
PQconsumeInput(), and/or PQnotifies().
Please also pardon me for asking inappropriate questions like this one. As far
as I can recall, every issue I encountered before always finally proved that
PostgreSQL is flawless.
Best Regards,
CN
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Getting reference key elements in right orders
Hi! I need to know the match columns of referencing and referenced keys. CREATE TABLE referenced (c1 TEXT,c2 TEXT, PRIMARY KEY(c1,c2)); CREATE TABLE referencing (c1 TEXT,c2 TEXT,c3 TEXT, PRIMARY KEY(c1,c2,c3), CONSTRAINT fk FOREIGN KEY (c1,c2) REFERENCES referenced (c1,c2)); The following SQL is similar to pg_get_constraintdef(): SELECT ARRAY_TO_STRING(ARRAY(SELECT attname FROM pg_attribute WHERE attrelid=master.oid AND attnum=ANY(confkey)),';') AS master_columns ,ARRAY_TO_STRING(ARRAY(SELECT attname FROM pg_attribute WHERE attrelid=detail.oid AND attnum=ANY(conkey)),';') AS detail_columns FROM pg_class master,pg_class detail,pg_constraint WHERE master.relname='referenced' AND detail.relname='referencing' AND confrelid=master.oid AND conrelid=detail.oid AND contype='f' AND confupdtype='c' AND confdeltype='c' It appears to do the job like this: master_columns detail_columns -- c1;c2 c1;c2 However, I am not sure the referencing and referenced key elements in the above selected strings, master_columns and detail_columns, are guaranteed to be in correct order. I suspect they will become these from time to time: master_columns detail_columns -- c1;c2 c2;c1 I am thinking that sorting subscripts of array "pg_constraint.confkey" should guarantee the correct order, but I have no idea how to do that. My questions are: Is the above SQL reliable? If it is not, how to make it reliable? Thank you in advance! CN -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Time zone 'GMT+8'
The target pgsql is compiled from 8.4rc1. 'GMT+8' can be found in installed binary file .../share/postgresql/timezone/Etc/GMT+8. This is the recorded script: -BEGIN record-- db1=# select now(); now --- 2009-11-29 14:44:37.322414+08 (1 row) db1=# set timezone to 'GMT+8'; SET db1=# select now(); now --- 2009-11-28 22:45:03.397545-08 (1 row) db1=# set timezone to GMT-8'; SET db1=# select now(); now --- 2009-11-29 14:45:39.160701+08 (1 row) db1=# set timezone to '0'; SET db1=# select now(); now --- 2009-11-29 06:45:54.347482+00 (1 row) -END record-- I thought time zone 'GMT+8' was '8' or UTC+8, and 'GMT-8' was '-8' or UTC-8. Does the original time zone settings shipped with source have special interpretation that is different from my understanding? Regards, CN -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Inserting exported bytea value
I am trying to build SQL commands that insert values exported from a bytea
column. Output facilities do not escape single quotes for bytea column. As
such, the built INSERT SQL can be invalid because single quotes can appear in
values. For example, the built (invalid) INSERT SQL command can look like this:
INSERT INTO t1 VALUES ('ABC\\000C'DE');
Note the apostrophe between C and D.
Are there straight approaches to do this job using version 8.x?
Does the bytea hex export feature provided by version 9 help solve this problem?
Thank you in advance!
CN
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can not match 0 on bytea
Hi! Data type of table1.c1 is bytea. That column stores binary data. The following matchings do not work. What is the right syntax? TIA CN --- select c1 ~ E'\000' from table1; select c1 LIKE E'%\000%' from table1; ERROR: invalid byte sequence for encoding "UTF8": 0x00 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] regexp_replace: LF, CR, or tab
Hi! I am trying to replace characters '\r', '\n', or '\t' with space character ' '. As an example, I want string "A\t\n\rB" becomes "AB". The following statement seems to be not working. What mistake have I made? TIA CN select regexp_replace(E'A\r\n\tB',E'[\r\n\t]',' '); regexp_replace A B (1 row) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Libpq: copy file to bytea column
Hi! The data types of tableout.c1 and tablein.c1 are both bytea. I first export tableout.c1 to a file: db1=# COPY (SELECT c1 FROM tableout LIMIT 1) TO '/tmp/t'; Then I try to import the file to another table. This works without flaw: db1=# COPY tablein FROM '/tmp/t'; However, I get the following errors from log when calling libpq functions PQputCopyData() and PQputCopyEnd(). 2010-03-06 20:47:42 CST ERROR: invalid byte sequence for encoding "UTF8": 0x00 2010-03-06 20:47:42 CST HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". 2010-03-06 20:47:42 CST CONTEXT: COPY in, line 1: "TPF0\011Report\000\003Tag\002\365\016ExportFromPage\002\000\014ExportToPage\002\000\006Values\016..." 2010-03-06 20:47:42 CST STATEMENT: COPY in FROM STDIN Helps will be much appreciated! CN -- 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] Libpq: copy file to bytea column
> > The data types of tableout.c1 and tablein.c1 are both bytea.
> > I first export tableout.c1 to a file:
> >
> > db1=# COPY (SELECT c1 FROM tableout LIMIT 1) TO '/tmp/t';
> >
> > Then I try to import the file to another table.
> >
> > This works without flaw:
> >
> > db1=# COPY tablein FROM '/tmp/t';
> >
> > However, I get the following errors from log when calling
> > libpq functions PQputCopyData() and PQputCopyEnd().
> >
> > 2010-03-06 20:47:42 CST ERROR: invalid byte sequence for
> > encoding "UTF8": 0x00
> > 2010-03-06 20:47:42 CST HINT: This error can also happen if
> > the byte sequence does not match the encoding expected by the
> > server, which is controlled by "client_encoding".
> > 2010-03-06 20:47:42 CST CONTEXT: COPY in, line 1:
> > "TPF0\011Report\000\003Tag\002\365\016ExportFromPage\002\000\014ExportToPage\002\000\006Values\016..."
> > 2010-03-06 20:47:42 CST STATEMENT: COPY in FROM STDIN
>
> The difference here is that your first examples are COPY TO/FROM
> files on the database server, while your failing C code uses
> COPY FROP STDIN.
>
> You will not meet encoding problems with the first because
> the file /tmp/t is created in the server encoding, so if you read
> it later with a COPY FROM, it will automatically be in the encoding
> that COPY FROM expects.
>
> When you use COPY FROM STDIN, you send data from the client to the
> server, which is something quite different. Data sent from the client
> are expected to be in the client encoding, not in the server encoding.
>
> You can use the SQL statement "SET client_encoding = ..." to set the
> client encoding to the encoding of your data, or you can use the
> PGCLIENTENCODING environment variable to set it automatically.
>
> The error message you are getting indicates that you client_encoding
> is currently UTF8.
> By the way, the line quoted in your error message does not look like
> valid text at all - it contains "zero" bytes, and preceeding each
> string there seems to be a byte indicating the length of the string
> that follows.
Changing client encoding can not make PQputCopyData() work, either. The problem
seems to be caused by the fact that PQputCopyData() does not accept zero value
('\0' in C), which I think can not be encoded to any character regardless of
which client encoding is used.
Yes, the data I am trying to import is binary data. That is why I use bytea to
store it.
What I can't understand is why PQputCopyData() encodes incoming character
string, which was exported by "COPY ... TO '/tmp/t'" SQL command under psql
prompt, for bytea columns and refuse to accept or misinterpret zero value.
Below is the more or less self contained code that also yields the same problem.
#define BYTEA "abcd\\011\\000ABCDEFGHIJKLMNO"
int main()
{
PGconn *PgConnect;
PGresult *result;
PgConnect=PQconndb("host=localhost port=5432 dbname=db1 user=postgres
password=passwd");
if(!PgConnect)
puts("Fail to connect.");
else{
result=PQexec(PgConnect,"COPY tablein FROM STDIN");
if(IsPgsqlExecutionOk(NULL,result)){
if(PQputCopyData(PgConnect,BYTEA,20) == 1){
if(PQputCopyEnd(PgConnect,NULL) == 1)
puts("Done");
else
puts(PQerrorMessage(PgConnect));
}
else
puts(PQerrorMessage(PgConnect));
}
PQfinish(PgConnect);
}
}
--Errors from the code:---
ERROR: invalid byte sequence for encoding "UTF8": 0x00
HINT: This error can also happen if the byte sequence does not match the
encoding expected by the server, which is controlled by "client_encoding".
CONTEXT: COPY tablein, line 1: "abcd\011\000ABCDEFGH"
STATEMENT: COPY tablein FROM STDIN
Another issue I can not understand is that both PQputCopyData() and
PQputCopyEnd() always return 1 even though the copy operation actually fails.
That being said, the above code always shows "Done" but actually error occurs.
Regards,
CN
--
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] Libpq: copy file to bytea column
> What I can't understand is why PQputCopyData() encodes incoming character > string, which was exported by "COPY ... TO '/tmp/t'" SQL command under psql > prompt, for bytea columns and refuse to accept or misinterpret zero value. As far as PQputCopyData() is concerned, server and/or client does not have to decode input character string for bytea column, does they? My understanding is that all client or server need to do to import data to bytea column is un-escaping all sets of \nnn (or \x000) character strings back to binary bytes and unconditionally store the result to the target bytea column. Encoding/Decoding is required only by TEXT and (VAR)CHAR columns, isn't it? Errors like the following ones should not occur to bytea columns, should they? ERROR: invalid byte sequence for encoding "UTF8": 0x00 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". CONTEXT: COPY tablein, line 1: "abcd\011\000ABCDEFGH" STATEMENT: COPY tablein FROM STDIN Regards, CN -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] unnest array of row type
Hi! CREATE TYPE my_row_type AS (c1 SMALLINT,c2 TEXT); CREATE FUNCTION tmp_get_c1(my_row_type) RETURNS SMALLINT AS 'SELECT $1.c1' LANGUAGE SQL; CREATE FUNCTION tmp_get_c2(my_row_type) RETURNS TEXT AS 'SELECT $1.c2' LANGUAGE SQL; CREATE TABLE test (c1 SMALLINT,c2 TEXT,c3 TEXT); INSERT INTO test SELECT tmp_get_c1(r),tmp_get_c2(r),'x' FROM ( SELECT UNNEST(ARRAY[ROW(1::SMALLINT,'a'::TEXT),ROW(2::SMALLINT,'b'::TEXT)]) AS r ) s; I get error "record type has not been registered" from the previous INSERT. I have tested version 9.1 and have confirmed that PL/PGSQL "FOREACH ... IN ARRAY ... LOOP ... END LOOP;" does the job. Because I wonder "INSERT INTO ... SELECT ... FROM" being faster than "LOOP ... END LOOP;", I raise this question. Thank you in advance! CN -- 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] unnest array of row type
What I really intend to do is slightly more complicate than the original code. I need to iterate RECORD variable in PL/pgSQL. By combining both ideas from Pavel and Merlin, I get the following working function. CREATE FUNCTION test() RETURNS VOID AS $$ DECLARE rec RECORD; BEGIN CREATE TABLE test (c1 SMALLINT,c2 TEXT,c3 TEXT); CREATE TYPE my_row_type AS (c1 SMALLINT,c2 TEXT); FOR rec IN SELECT ARRAY[ROW(1::SMALLINT,'a'::TEXT),ROW(2::SMALLINT,'b'::TEXT)] AS array_of_rows,'x' AS x LOOP INSERT INTO test SELECT (r).c1,(r).c2,rec.x FROM ( SELECT (c1,c2)::my_row_type AS r FROM UNNEST(rec.array_of_rows) AS (c1 SMALLINT,c2 TEXT) ) s; END LOOP; DROP TYPE my_row_type; DROP TABLE test; END $$ LANGUAGE PLPGSQL VOLATILE; Pavel and Merlin, thank you! CN > -Original Message- > From: Merlin Moncure > Sent: Fri, Jan 13 2012 01:13:09 CST > To: Pavel Stehule > Subject: Re: [GENERAL] unnest array of row type > > On Thu, Jan 12, 2012 at 8:47 AM, Pavel Stehule > wrote: > > 2012/1/12 : > >> Hi! > >> > >> CREATE TYPE my_row_type AS (c1 SMALLINT,c2 TEXT); > >> > >> CREATE FUNCTION tmp_get_c1(my_row_type) RETURNS SMALLINT AS 'SELECT $1.c1' > >> LANGUAGE SQL; > >> > >> CREATE FUNCTION tmp_get_c2(my_row_type) RETURNS TEXT AS 'SELECT $1.c2' > >> LANGUAGE SQL; > >> > >> CREATE TABLE test (c1 SMALLINT,c2 TEXT,c3 TEXT); > >> > >> INSERT INTO test > >> SELECT tmp_get_c1(r),tmp_get_c2(r),'x' > >> FROM ( > >> SELECT > >> UNNEST(ARRAY[ROW(1::SMALLINT,'a'::TEXT),ROW(2::SMALLINT,'b'::TEXT)]) AS r > >> ) s; > >> > >> I get error "record type has not been registered" from the previous INSERT. > >> > >> I have tested version 9.1 and have confirmed that PL/PGSQL "FOREACH ... IN > >> ARRAY ... LOOP ... END LOOP;" does the job. Because I wonder "INSERT INTO > >> ... SELECT ... FROM" being faster than "LOOP ... END LOOP;", I raise this > >> question. > >> > > > > insert into test select tmp_get_c1(r), tmp_get_c2(r), 'x' from (SELECT > > (x,y)::my_row_type as r from > > UNNEST(ARRAY[ROW(1::SMALLINT,'a'::TEXT),ROW(2::SMALLINT,'b'::TEXT)]) > > AS (x smallint, y text)) x; > > I don't think the tmp_get* functions are necessary (and even if they > were, you should mark them 'immutable'). Also that's unnecessarily > verbose. I would write it like this: > > INSERT INTO test SELECT (r).c1, (r).c2, 'x' FROM > ( > SELECT UNNEST(ARRAY[ROW(1,'a'),ROW(2,'b')]::my_row_type[]) r > ) x; > > merlin > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Reserve one row for every distinct value in a column
Hi! I am trying to implement a mechanism to reserve the last row for every distinct value in column "c1". CREATE TABLE table1 (c1 INTEGER,c2 INTEGER, PRIMARY KEY (c1,c2)); CREATE OR REPLACE FUNCTION keep1() RETURNS TRIGGER AS $$ BEGIN IF (SELECT COUNT(*) FROM table1 WHERE c1=OLD.c1) = 1 THEN RAISE EXCEPTION 'The last row for c1 = % must be kept!',OLD.c1; END IF; RETURN OLD; END $$ LANGUAGE PLPGSQL STABLE; CREATE TRIGGER test BEFORE DELETE ON table1 FOR EACH ROW EXECUTE PROCEDURE keep1(); INSERT INTO table1 VALUES (1,1),(1,2),(1,3); With above rows inserted, my desired effect follows: (1) allowed user operations: DELETE FROM table1 WHERE c2 IN (1,2); /* Row (1,3) is still kept */ DELETE FROM table1 WHERE c2=2; DELETE FROM table1 WHERE c2=3; /* Row (1,1) is still kept */ DELETE FROM table1 WHERE c1=9; /* We have at least one row with c1=1 that is kept intact */ (2) disallowed user operations: DELETE FROM table1 WHERE c1=1; /* Exception wanted. Every row for c1=1 would be deleted otherwise. */ DELETE FROM table1 WHERE c2 IN (1,2); DELETE FROM table1 WHERE c2=3; /* The second DELETE must raise exception. */ The above trigger: (1) It raises exception if table1 has only one row (1,1) and I delete it. This gives expected effect. (2) When table1 contains 3 rows (1,1),(1,2),(1,3), then all of the following SQL yields unwanted result - they do not raise exception and I can not figure out why the trigger is silenced: DELETE FROM table1; DELETE FROM table1 WHERE c1=1; DELETE FROM table1 WHERE c2 IN (1,2,3); Would someone please provide me some idea for a working implementation? Thank you in advance! CN -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
