Re: [GENERAL] Using psql to feed a file line by line to a table column

2013-03-14 Thread Ian Lawrence Barwick
2013/3/13 Alexander Farber alexander.far...@gmail.com:
 Thank you, this was indeed the
 (uneeded) semicolon at end of the COPY line.

 May I ask another question -
(...)
 When I add few more words to my text file
 and then try to load it into my table again,
 then the COPY command will fail,
 because of the already stored words:

 bukvy= \copy good_words(word) from WORDS
 ERROR:  duplicate key value violates unique constraint good_words_pkey
 CONTEXT:  COPY good_words, line 1: абажур

 Can't I change the behaviour to silently
 ignore inserting such words?

 I also have an INSERT trigger on my table,
 can I return a NULL from it or something similar?

Yes, if you test for the presence of the word you can return NULL
and the row will be discarded. See example below.

Regards

Ian Barwick

testdb=# CREATE TABLE foo (word TEXT NOT NULL PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
foo_pkey for table foo
CREATE TABLE
testdb=#
testdb=# INSERT INTO foo VALUES('bar'),('baz');
INSERT 0 2
testdb=# INSERT INTO foo VALUES('bar');
ERROR:  duplicate key value violates unique constraint foo_pkey
DETAIL:  Key (word)=(bar) already exists.


CREATE OR REPLACE FUNCTION foo_check()
  RETURNS TRIGGER
  LANGUAGE 'plpgsql'
AS
$$
  BEGIN
PERFORM TRUE
  FROM foo
 WHERE word = NEW.word;
IF FOUND THEN
  RETURN NULL;
END IF;
RETURN NEW;
  END;
$$;

CREATE TRIGGER tr_foo_check
  BEFORE INSERT ON foo
  FOR EACH ROW EXECUTE PROCEDURE foo_check();

testdb=# INSERT INTO foo VALUES('bar');
INSERT 0 0


-- 
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] Using psql to feed a file line by line to a table column

2013-03-14 Thread Alexander Farber
Thank you -

On Thu, Mar 14, 2013 at 10:40 AM, Ian Lawrence Barwick
barw...@gmail.com wrote:
 I also have an INSERT trigger on my table,
 can I return a NULL from it or something similar?

 Yes, if you test for the presence of the word you can return NULL
 and the row will be discarded. See example below.


 testdb=# CREATE TABLE foo (word TEXT NOT NULL PRIMARY KEY);
 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
 foo_pkey for table foo
 CREATE TABLE
 testdb=#
 testdb=# INSERT INTO foo VALUES('bar'),('baz');
 INSERT 0 2
 testdb=# INSERT INTO foo VALUES('bar');
 ERROR:  duplicate key value violates unique constraint foo_pkey
 DETAIL:  Key (word)=(bar) already exists.


 CREATE OR REPLACE FUNCTION foo_check()
   RETURNS TRIGGER
   LANGUAGE 'plpgsql'
 AS
 $$
   BEGIN
 PERFORM TRUE
   FROM foo
  WHERE word = NEW.word;
 IF FOUND THEN
   RETURN NULL;
 END IF;
 RETURN NEW;
   END;
 $$;

 CREATE TRIGGER tr_foo_check
   BEFORE INSERT ON foo
   FOR EACH ROW EXECUTE PROCEDURE foo_check();

 testdb=# INSERT INTO foo VALUES('bar');
 INSERT 0 0

so the return value of an insert trigger
is actually what get's inserted?

And it has to be an BEFORE trigger?

Regards
Alex


-- 
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] Using psql to feed a file line by line to a table column

2013-03-14 Thread Ian Lawrence Barwick
2013/3/14 Alexander Farber alexander.far...@gmail.com:
 Thank you -

 On Thu, Mar 14, 2013 at 10:40 AM, Ian Lawrence Barwick
 barw...@gmail.com wrote:
 I also have an INSERT trigger on my table,
 can I return a NULL from it or something similar?

 Yes, if you test for the presence of the word you can return NULL
 and the row will be discarded. See example below.


 testdb=# CREATE TABLE foo (word TEXT NOT NULL PRIMARY KEY);
 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
 foo_pkey for table foo
 CREATE TABLE
 testdb=#
 testdb=# INSERT INTO foo VALUES('bar'),('baz');
 INSERT 0 2
 testdb=# INSERT INTO foo VALUES('bar');
 ERROR:  duplicate key value violates unique constraint foo_pkey
 DETAIL:  Key (word)=(bar) already exists.


 CREATE OR REPLACE FUNCTION foo_check()
   RETURNS TRIGGER
   LANGUAGE 'plpgsql'
 AS
 $$
   BEGIN
 PERFORM TRUE
   FROM foo
  WHERE word = NEW.word;
 IF FOUND THEN
   RETURN NULL;
 END IF;
 RETURN NEW;
   END;
 $$;

 CREATE TRIGGER tr_foo_check
   BEFORE INSERT ON foo
   FOR EACH ROW EXECUTE PROCEDURE foo_check();

 testdb=# INSERT INTO foo VALUES('bar');
 INSERT 0 0

 so the return value of an insert trigger
 is actually what get's inserted?

Yup, normally that would be the contents of the NEW record.

 And it has to be an BEFORE trigger?

Yes, because an AFTER trigger is fired after the row is updated, so
the row can't be changed.

Regards

Ian Barwick


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[GENERAL] Do not understand why this happens

2013-03-14 Thread Aln Kapa
HI.

I connect to the database using the PGDAC, and then send a NOTIFY to
myself every minute.  In this case, the logs get that.

2013-03-10 10:34:36 19797 LOG:  process 19797 still waiting for
AccessExclusiveLock on object 0 of class 1262 of database 0 after
3000.100 ms
2013-03-10 10:34:36 19797 STATEMENT:  NOTIFY test105, ''
2013-03-10 10:34:37 19797 LOG:  process 19797 acquired
AccessExclusiveLock on object 0 of class 1262 of database 0 after
3315.206 ms
2013-03-10 10:34:37 19797 STATEMENT:  NOTIFY test105, ''
2013-03-10 10:34:37 19797 LOG:  duration: 3315.322 ms  statement:
NOTIFY test105, ''
2013-03-10 14:27:43 19797 LOG:  could not receive data from client:
Connection reset by peer
2013-03-10 14:27:43 19797 LOG:  unexpected EOF on client connection

Tell me what's the problem?


-- 
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] Do not understand why this happens

2013-03-14 Thread Bill Moran
On Thu, 14 Mar 2013 15:24:45 +0400 Aln Kapa alnk...@gmail.com wrote:
 
 I connect to the database using the PGDAC, and then send a NOTIFY to
 myself every minute.  In this case, the logs get that.
 
 2013-03-10 10:34:36 19797 LOG:  process 19797 still waiting for
 AccessExclusiveLock on object 0 of class 1262 of database 0 after
 3000.100 ms
 2013-03-10 10:34:36 19797 STATEMENT:  NOTIFY test105, ''
 2013-03-10 10:34:37 19797 LOG:  process 19797 acquired
 AccessExclusiveLock on object 0 of class 1262 of database 0 after
 3315.206 ms
 2013-03-10 10:34:37 19797 STATEMENT:  NOTIFY test105, ''
 2013-03-10 10:34:37 19797 LOG:  duration: 3315.322 ms  statement:
 NOTIFY test105, ''
 2013-03-10 14:27:43 19797 LOG:  could not receive data from client:
 Connection reset by peer
 2013-03-10 14:27:43 19797 LOG:  unexpected EOF on client connection
 
 Tell me what's the problem?

Are the last two lines your perceived problem?  Is the NOTIFY working?

There's really not enough information here to actually understand
what you're asking.  Based on the log information, it looks like
a client program issues a notify, then drops the connection.  Could
be because in intervening network control device times out the TCP
state, or could be because the client drops the conneciton, or
because the client crashed, or is poorly implementd in that it looses
its TCP socket.

In any event, the 4 hour lag between the NOTIFY and the conneciton drop
during which nothing happens seems to indicate that the two events
are probably not related.

Is any of that helpful?  I feel like I don't understand your question
and suspect that you didn't receive an answer to your first post
because most people didn't understand it.  If my comments don't
address your question, perhaps try describing it differently.

-- 
Bill Moran wmo...@potentialtech.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] big un stacking query - help save me from myself

2013-03-14 Thread Kevin Grittner
Kirk Wythers kirk.wyth...@gmail.com wrote:

 I hace a fairly large table with two columns that I need to
 de-normalize (235 million rows) There has got to be a better
 (i.e. faster) approach than what I am doing. I am using a MAX
 CASE on each of the 24 variables (column names variable and
 value) that I want to unstack.  Any suggestions would be most
 appreciated.

I didn't understand your description of what you are trying to do,
and the example has so many columns and cases that it would take a
long time to understand it.  Can you distill this down to just a
few columns and cases so that it is easier to understand what you
are trying to accomplish?  Even better would be a self-contained
test case with just a few rows so people can see before and
after data.  What you have already posted will help give context
on how it needs to scale, which is important, too; but if you make
the issue easier to understand, the odds improve that someone will
volunteer the time needed to make a suggestion.

-Kevin

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Do not understand why this happens

2013-03-14 Thread Vick Khera
On Wed, Mar 13, 2013 at 8:08 AM, Aln Kapa alnk...@gmail.com wrote:

 2013-03-10 14:27:43 19797 LOG:  could not receive data from client:
 Connection reset by peer
 2013-03-10 14:27:43 19797 LOG:  unexpected EOF on client connection

 Tell me what's the problem?


Your client closed the socket without cleanly closing the connection to
postgres. did it crash?


[GENERAL] Fedora 18 x86_64 repodata/repomd.xml: [Errno 14] curl#22 - The requested URL returned error: 404 Not Found on PostgreSQL 9.2

2013-03-14 Thread René Romero Benavides
Using the yum repository for PostgreSQL 9.2, does anybody know what happened 
to  http://yum.postgresql.org/9.2/fedora/fedora-18-x86_64/repodata/repomd.xml?

-- 
René Romero Benavides @iCodeiExist @PgsqlMx 

Postgresql Tips en español para la comunidad de México e Hispanoamérica.
http://postgresql.org.mx 







Re: [GENERAL] Fedora 18 x86_64 repodata/repomd.xml: [Errno 14] curl#22 - The requested URL returned error: 404 Not Found on PostgreSQL 9.2

2013-03-14 Thread Devrim Gündüz
Hi,

No idea what happened, but fixed. Thanks for the report.

Regards, Devrim

René Romero Benavides ichbinr...@gmail.com wrote:

Using the yum repository for PostgreSQL 9.2, does anybody know what
happened 
to 
http://yum.postgresql.org/9.2/fedora/fedora-18-x86_64/repodata/repomd.xml?

-- 
René Romero Benavides @iCodeiExist @PgsqlMx 

Postgresql Tips en español para la comunidad de México e
Hispanoamérica.
http://postgresql.org.mx 

--
Devrim Gündüz