[GENERAL] Trouble with PQnotifies()

[seiliki]

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

Re: [GENERAL] Corrupt indexes on slave when using pg_bulkload on master

[James Cowell]

Hi Jeff,
 
Thanks again for your reply.
 
>If there are no constraint violations, do you still see the problem?
 
Yes, I've stripped it down to an empty table with a 10 row load and the pk 
index on the secondary node still corrupts.
 
> Were there any older version on which it worked? 
 
I'm afraid I started on 9.1.5, I upgraded to 9.1.6 when I had the initial 
problem due to the bugfix in the changelog to do with corrupt indexes on the 
secondary node but it hasn't resolved the issue.
 
> Can you post a minimal schema and control file to reproduce the problem?
 
I've attached a text file with details for table, load config file etc, is that 
everything you would need?
 
Cheers,
 
James
 


 From: Jeff Janes 
To: James Cowell  
Cc: "pgsql-general@postgresql.org"  
Sent: Monday, 10 December 2012, 16:53
Subject: Re: [GENERAL] Corrupt indexes on slave when using pg_bulkload on master
  
On Wed, Dec 5, 2012 at 5:17 AM, James Cowell  wrote:
> I'm using pg_bulkload to load large amounts of CSV data into a postgres
> database hourly.
>
> This database is replicated to a second node.
>
> Whenever a bulk load happens the indexes on the updated tables on the
> secondary node corrupt and are unusable until a reindex is run on the
> primary node.  I get the error below on node 2:
>
> ERROR: index "tablename" contains unexpected zero page at block 0
> SQL state: XX002
> Hint: Please REINDEX it.
>
> I'm assuming that this is because of the way pg_bulkload builds the index on
> the primary, and possibly has something to do with the way pg_bulkload
> overwrites rows in the event of a constraint violation,

If there are no constraint violations, do you still see the problem?

> but at the same time
> if something works on the primary shouldn't the replicated node be able to
> process the WAL log?
>
> I've tried this on 9.1.6 and 9.1.5 on RHEL 6.3 with pg_bulkload build
> 3.1.1-1.pg91.rhel6 and it happens every time.

Were there any older version on which it worked?  Can you post a
minimal schema and control file to reproduce the problem?

Cheers,

JeffCREATE TABLE:
-

   CREATE TABLE public.bulkreptest
   (
 route integer NOT NULL,
 dtg timestamp without time zone NOT NULL,
 issue_dtg timestamp without time zone NOT NULL,
 insert_dtg timestamp without time zone,
 just_a_number numeric(4,2)
 CONSTRAINT pk_bulkreptest PRIMARY KEY (route , issue_dtg , dtg )
   
   )
   WITH (
 OIDS=FALSE
   );
   ALTER TABLE public.bulkreptest
 OWNER TO postgres;
   GRANT ALL ON TABLE public.bulkreptest TO postgres;
   
CREATE BULKLOAD FILTER:
---

CREATE OR REPLACE FUNCTION public.bulkreptest_filter(integer, timestamp without 
time zone, timestamp without time zone, timestamp without time zone, numeric)
  RETURNS record AS
$BODY$ 
SELECT $1, $2, $3, now(), $5 
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION public.bulkreptest_filter(integer, timestamp without time zone, 
timestamp without time zone, timestamp without time zone, numeric)
  OWNER TO postgres;
  
CREATE BULKLOAD CONFIG FILE: (change db name)
-

OUTPUT = *YOUR_DB_NAME*.public.bulkreptest
TYPE = CSV
QUOTE = "\""
ESCAPE = \
DELIMITER = ","
LOADER = BUFFERED
ON_DUPLICATE_KEEP = NEW
DUPLICATE_ERRORS = -1
FILTER = *YOUR_DB_NAME*.public.bulkreptest_filter

CREATE LOAD DATA FILE:
--

100,2012-12-13 10:00,2012-12-13 10:00,,1.498
100,2012-12-13 11:00,2012-12-13 10:00,,2.599
100,2012-12-13 12:00,2012-12-13 10:00,,3.426
100,2012-12-13 13:00,2012-12-13 10:00,,3.935
100,2012-12-13 14:00,2012-12-13 10:00,,4.008
100,2012-12-13 15:00,2012-12-13 10:00,,3.417
100,2012-12-13 16:00,2012-12-13 10:00,,2.507
100,2012-12-13 17:00,2012-12-13 10:00,,1.956
100,2012-12-13 18:00,2012-12-13 10:00,,1.699
100,2012-12-13 19:00,2012-12-13 10:00,,1.944

BULKLOAD COMMAND:
-

/usr/pgsql-9.1/bin/pg_bulkload -d *YOUR_DB_NAME* -i /path/to/data/file.txt 
/path/to/load/config.cfg 2>> /path/to/load/logfile.log

SQL COMMAND:


On the primary node

select * from bulkreptest where issue_dtg>='2012-12-12 00:00' and 
issue_dtg<='2012-12-14 00:00' order by route,issue_dtg,dtg

100;"2012-12-13 10:00:00";"2012-12-13 10:00:00";"2012-12-13 
11:46:31.097496";1.498
100;"2012-12-13 11:00:00";"2012-12-13 10:00:00";"2012-12-13 
11:46:31.097496";2.599
100;"2012-12-13 12:00:00";"2012-12-13 10:00:00";"2012-12-13 
11:46:31.097496";3.426
100;"2012-12-13 13:00:00";"2012-12-13 10:00:00";"2012-12-13 
11:46:31.097496";3.935
100;"2012-12-13 14:00:00";"2012-12-13 10:00:00";"2012-12-13 
11:46:31.097496";4.008
100;"2012-12-13 15:00:00";"2012-12-13 10:00:00";"2012-12-13 
11:46:31.097496";3.417
100;"2012-12-13 16:00:00";"2012-12-13 10:00:00";"2012-12-13 
11:46:31.097496";2.507
100;"2012-12-13 17:00:00";"2012-12-13 10:00:00";"2012-12-13 
11:46:31.097496";1.956
100;"2012-12-13 18:00:00";"2012-12-13 10:00:00";"2012-12-13 
11:46:31.097496";1.6

[GENERAL] How to keep the last row of a data set?

[seiliki]

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] Corrupt indexes on slave when using pg_bulkload on master

[wd]

We encounter the same problem, and have to change to use copy command


On Wed, Dec 5, 2012 at 9:17 PM, James Cowell  wrote:

> I'm using pg_bulkload to load large amounts of CSV data into a postgres
> database hourly.
>
> This database is replicated to a second node.
>
> Whenever a bulk load happens the indexes on the updated tables on the
> secondary node corrupt and are unusable until a reindex is run on the
> primary node.  I get the error below on node 2:
>
> ERROR: index "tablename" contains unexpected zero page at block 0
> SQL state: XX002
> Hint: Please REINDEX it.
>
> I'm assuming that this is because of the way pg_bulkload builds the index
> on the primary, and possibly has something to do with the way pg_bulkload
> overwrites rows in the event of a constraint violation, but at the same
> time if something works on the primary shouldn't the replicated node be
> able to process the WAL log?
>
> I've tried this on 9.1.6 and 9.1.5 on RHEL 6.3 with pg_bulkload build
> 3.1.1-1.pg91.rhel6 and it happens every time.
>
> Does anyone have any experience in this area or advice they could give?
> If you can point out something stupid I'm doing that would be very welcome
> :)
>
> Thanks,
>
> James
>

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Emi Lu]

Is there a simple way to load UTF8 data in psql to mysql(with latin1
encoding) through JDBC?


All you need to do is to query the source database, then use
ResultSet.getString() to obtain the data and use a PreparedStatement and
PreparedStatement.setString() to insert/update  the data on the target
database.

The JDBC drivers will handle all the conversion.
Do NOT manually convert the data.

getString() and setString() will do everything correctly.


I am not using stmt directly but through Mybatis for all db transactions.

So, this approach will not work.

Thanks.
--
Emi




--
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] JDBC to load UTF8@psql to latin1@mysql

[Edson Richter]

Em 13/12/2012 12:00, Emi Lu escreveu:



Is there a simple way to load UTF8 data in psql to mysql(with latin1
encoding) through JDBC?


All you need to do is to query the source database, then use
ResultSet.getString() to obtain the data and use a PreparedStatement and
PreparedStatement.setString() to insert/update  the data on the target
database.

The JDBC drivers will handle all the conversion.
Do NOT manually convert the data.

getString() and setString() will do everything correctly.


I am not using stmt directly but through Mybatis for all db transactions.


Should not this a Mybatis problem instead?
As stated, JDBC drivers does all the conversion needed automatically, 
but if you have a middleware messing with your enconding, then the 
problem is the middleware, not databases or drivers.


Edson



So, this approach will not work.

Thanks.
--
Emi








--
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] JDBC to load UTF8@psql to latin1@mysql

[Emi Lu]

I don't think your Java code does what you think it does. You should
read some more about how Java handles string encodings. Here is a method
I wrote some years ago that might also help you. It converts streams,
not strings, but what you need should be pretty close (and simpler):
   /**
* Interprets in according to encIn, and converts it to encOut,
* writing to out. Allocates buffer for the buffer size.
* @param encIn The input encoding.
* @param encOut The output encoding.
* @param in The data to convert.
* @param out Where to send the converted data.
* @param buffer The size of the buffer or 0 for the default.
* @throws IOException
*/
   public void run(String encIn, String encOut, InputStream in,
OutputStream out, int buffer) throws IOException {
 Reader r = null;
 Writer w = null;
 int len;
 char[]  b;
 try {
   if (buffer > 0) {
 r = new BufferedReader(new InputStreamReader(in, encIn), buffer);
 w = new BufferedWriter(new OutputStreamWriter(out, encOut),
buffer);
   } else {
 r = new BufferedReader(new InputStreamReader(in, encIn));
 w = new BufferedWriter(new OutputStreamWriter(out, encOut));
 buffer = DEFAULT_BUFFER_SIZE;
   }
   b = new char[buffer];

   while ((len = r.read(b, 0, buffer)) != -1) {
 w.write(b, 0, len);
   }
 } finally {
   try {
 if (r != null) r.close();
   } finally {
 if (w != null) w.close();
   }
 }
   }
Btw, none of this has anything to do with Postgres. :-)
Thank you for the code first. I will try it later. The problem I had as 
mentioned in the subject is:

(1) psql@utf8
(2) mysql@latin1

When I load data from (1) to (2) through Mybatis, french characters 
could not be mapped correctly in (2). I was thinking that psql may have 
methods could help this. But it seems that I have to try from java 
coding side :-(


--
Emi


--
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?

[Thomas Markus]

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



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

[GENERAL] initdb error

[David Noel]

I'm running into the following error message when running initdb (FreeBSD host):

ygg# /usr/local/etc/rc.d/postgresql initdb -D /zdb/pgsql/data --debug
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.

The database cluster will be initialized with locales
  COLLATE:  C
  CTYPE:en_US.UTF-8
  MESSAGES: en_US.UTF-8
  MONETARY: en_US.UTF-8
  NUMERIC:  en_US.UTF-8
  TIME: en_US.UTF-8
The default text search configuration will be set to "english".

creating directory /zdb/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/00010001" (log file 0,
segment 1): No such file or directory
child process exited with exit code 1
initdb: removing data directory "/zdb/pgsql/data"

My best guess is that it has something to do with permissions, but I
really have no idea. Has anyone seen this before and found a way
around it?

-David


-- 
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?

[seiliki]

> -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] initdb error

[Adrian Klaver]

On 12/13/2012 07:38 AM, David Noel wrote:

I'm running into the following error message when running initdb (FreeBSD host):

ygg# /usr/local/etc/rc.d/postgresql initdb -D /zdb/pgsql/data --debug
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.

The database cluster will be initialized with locales
   COLLATE:  C
   CTYPE:en_US.UTF-8
   MESSAGES: en_US.UTF-8
   MONETARY: en_US.UTF-8
   NUMERIC:  en_US.UTF-8
   TIME: en_US.UTF-8
The default text search configuration will be set to "english".

creating directory /zdb/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/00010001" (log file 0,
segment 1): No such file or directory
child process exited with exit code 1
initdb: removing data directory "/zdb/pgsql/data"

My best guess is that it has something to do with permissions, but I
really have no idea. Has anyone seen this before and found a way
around it?



Not quite what is in the the init script in rc.d, have you tried running 
the initdb command directly? As you say I believe there are permissions 
problems. So following the instructions found below may solve your problem:


http://www.postgresql.org/docs/9.2/interactive/app-initdb.html





-David





--
Adrian Klaver
adrian.kla...@gmail.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] How to keep the last row of a data set?

[Tom Lane]

seil...@so-net.net.tw writes:
> I am trying to implement a mechanism that prohibits the last row of a data 
> set from being deleted.

> 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;

The reason that doesn't work is you marked it "stable", so it always
sees the starting state of the outer query.

Mind you, even with that oversight fixed, this approach will do little
to save you from concurrent-update situations.  That is, transaction A
could delete some of the rows with c1=1, and transaction B could
concurrently delete the rest, and neither transaction will see a reason
why it shouldn't commit.

regards, tom lane


-- 
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] initdb error

[David Noel]

On 12/13/12, Adrian Klaver  wrote:
> On 12/13/2012 07:38 AM, David Noel wrote:
>> I'm running into the following error message when running initdb (FreeBSD
>> host):
>>
>> ygg# /usr/local/etc/rc.d/postgresql initdb -D /zdb/pgsql/data --debug
>> The files belonging to this database system will be owned by user
>> "pgsql".
>> This user must also own the server process.
>>
>> The database cluster will be initialized with locales
>>COLLATE:  C
>>CTYPE:en_US.UTF-8
>>MESSAGES: en_US.UTF-8
>>MONETARY: en_US.UTF-8
>>NUMERIC:  en_US.UTF-8
>>TIME: en_US.UTF-8
>> The default text search configuration will be set to "english".
>>
>> creating directory /zdb/pgsql/data ... ok
>> creating subdirectories ... ok
>> selecting default max_connections ... 100
>> selecting default shared_buffers ... 32MB
>> creating configuration files ... ok
>> creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
>> could not open file "pg_xlog/00010001" (log file 0,
>> segment 1): No such file or directory
>> child process exited with exit code 1
>> initdb: removing data directory "/zdb/pgsql/data"
>>
>> My best guess is that it has something to do with permissions, but I
>> really have no idea. Has anyone seen this before and found a way
>> around it?
>
>
> Not quite what is in the the init script in rc.d, have you tried running
> the initdb command directly? As you say I believe there are permissions
> problems. So following the instructions found below may solve your problem:
>
> http://www.postgresql.org/docs/9.2/interactive/app-initdb.html
>
>
>
>>
>> -David
>>
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>


I've tried initdb directly:

initdb -D /zdb/pgsql/data

...and also through pg_ctl:

pg_ctl initdb -D /zdb/pgsql/data

...and still seem to wind up with the error:

creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/00010001" (log file 0,
segment 1): No such file or directory

-David


-- 
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] initdb error

[Adrian Klaver]

On 12/13/2012 08:18 AM, David Noel wrote:

On 12/13/12, Adrian Klaver  wrote:

On 12/13/2012 07:38 AM, David Noel wrote:

I'm running into the following error message when running initdb (FreeBSD
host):

ygg# /usr/local/etc/rc.d/postgresql initdb -D /zdb/pgsql/data --debug
The files belonging to this database system will be owned by user
"pgsql".
This user must also own the server process.

The database cluster will be initialized with locales
COLLATE:  C
CTYPE:en_US.UTF-8
MESSAGES: en_US.UTF-8
MONETARY: en_US.UTF-8
NUMERIC:  en_US.UTF-8
TIME: en_US.UTF-8
The default text search configuration will be set to "english".

creating directory /zdb/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/00010001" (log file 0,
segment 1): No such file or directory
child process exited with exit code 1
initdb: removing data directory "/zdb/pgsql/data"

My best guess is that it has something to do with permissions, but I
really have no idea. Has anyone seen this before and found a way
around it?



Not quite what is in the the init script in rc.d, have you tried running
the initdb command directly? As you say I believe there are permissions
problems. So following the instructions found below may solve your problem:

http://www.postgresql.org/docs/9.2/interactive/app-initdb.html





-David





--
Adrian Klaver
adrian.kla...@gmail.com




I've tried initdb directly:

initdb -D /zdb/pgsql/data

...and also through pg_ctl:

pg_ctl initdb -D /zdb/pgsql/data

...and still seem to wind up with the error:

creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/00010001" (log file 0,
segment 1): No such file or directory


You are doing the above as the database user ex:postgres?

The database user has permissions on /zdb/pgsql/data?



-David





--
Adrian Klaver
adrian.kla...@gmail.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] initdb error

[Tom Lane]

David Noel  writes:
> I've tried initdb directly:
> initdb -D /zdb/pgsql/data
> ...and still seem to wind up with the error:
> creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
> could not open file "pg_xlog/00010001" (log file 0,
> segment 1): No such file or directory

Hm, that eliminates my first theory that the rc.d script is using
initdb's --xlogdir switch to try to put pg_xlog someplace that doesn't
exist on your system.  But it seems like this must be some variant of
that.  A simple permissions problem is not very credible, because at
this point initdb has successfully made the data directory and a bunch
of files within it already.

You could get more information by using the --noclean switch to prevent
removal of the datadir after failure, and then having a look at the
debris.  Is there a pg_xlog subdirectory inside /zdb/pgsql/data, and if
so what permissions has it got?  Perhaps it is not a directory, but a
symlink to somewhere else?

regards, tom lane


-- 
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] initdb error

[Amitabh Kant]

On Thu, Dec 13, 2012 at 10:00 PM, Adrian Klaver wrote:

> On 12/13/2012 08:18 AM, David Noel wrote:
>
>> On 12/13/12, Adrian Klaver  wrote:
>>
>>> On 12/13/2012 07:38 AM, David Noel wrote:
>>>
 I'm running into the following error message when running initdb
 (FreeBSD
 host):

 ygg# /usr/local/etc/rc.d/postgresql initdb -D /zdb/pgsql/data --debug
 The files belonging to this database system will be owned by user
 "pgsql".
 This user must also own the server process.

 The database cluster will be initialized with locales
 COLLATE:  C
 CTYPE:en_US.UTF-8
 MESSAGES: en_US.UTF-8
 MONETARY: en_US.UTF-8
 NUMERIC:  en_US.UTF-8
 TIME: en_US.UTF-8
 The default text search configuration will be set to "english".

 creating directory /zdb/pgsql/data ... ok
 creating subdirectories ... ok
 selecting default max_connections ... 100
 selecting default shared_buffers ... 32MB
 creating configuration files ... ok
 creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
 could not open file "pg_xlog/**00010001" (log file 0,
 segment 1): No such file or directory
 child process exited with exit code 1
 initdb: removing data directory "/zdb/pgsql/data"

 My best guess is that it has something to do with permissions, but I
 really have no idea. Has anyone seen this before and found a way
 around it?

>>>
>>>
>>> Not quite what is in the the init script in rc.d, have you tried running
>>> the initdb command directly? As you say I believe there are permissions
>>> problems. So following the instructions found below may solve your
>>> problem:
>>>
>>> http://www.postgresql.org/**docs/9.2/interactive/app-**initdb.html
>>>
>>>
>>>
>>>
 -David



>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@gmail.com
>>>
>>>
>>
>> I've tried initdb directly:
>>
>> initdb -D /zdb/pgsql/data
>>
>> ...and also through pg_ctl:
>>
>> pg_ctl initdb -D /zdb/pgsql/data
>>
>> ...and still seem to wind up with the error:
>>
>> creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
>> could not open file "pg_xlog/**00010001" (log file 0,
>> segment 1): No such file or directory
>>
>
> You are doing the above as the database user ex:postgres?
>
> The database user has permissions on /zdb/pgsql/data?
>
>
>
>> -David
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>
>
The rc.d script executes the command as the correct user. This is the line
in rc.d script that runs the initdb command:
su -l -c ${postgresql_class} ${postgresql_user} -c "exec
/usr/local/bin/initdb ${postgresql_initdb_flags} -D ${postgresql_data}"

Not sure about his permission though

Re: [GENERAL] How to keep the last row of a data set?

[Kevin Grittner]

Tom Lane wrote:
> seil...@so-net.net.tw writes:
>> I am trying to implement a mechanism that prohibits the last row
>> of a data set from being deleted.

> The reason that doesn't work is you marked it "stable", so it
> always sees the starting state of the outer query.
> 
> Mind you, even with that oversight fixed, this approach will do
> little to save you from concurrent-update situations. That is,
> transaction A could delete some of the rows with c1=1, and
> transaction B could concurrently delete the rest, and neither
> transaction will see a reason why it shouldn't commit.

Right, that is a form of write skew, where each transaction is
writing to the database (in this case with deletes) based on
reading a portion of the database written to by the other
transaction. This will be handled automatically if all transactions
are using transaction isolation level SERIALIZABLE. Otherwise you
need to materialize the conflict (for example, by adding a separate
table with one row per c1 value, and a count of matching t1 rows,
maintained by triggers) or promote the conflict (changing the
non-blocking read-write conflicts into write-write conflicts, by
updating all the rows with the same c1 value which you are not
deleting). Or you could use table-level blocking with LOCK TABLE
statements, or develop some scheme to use advisory locks.

These pages might help:

http://www.postgresql.org/docs/9.2/interactive/mvcc.html

http://wiki.postgresql.org/wiki/SSI

-Kevin


-- 
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] initdb error

[Adrian Klaver]

On 12/13/2012 08:35 AM, Tom Lane wrote:

David Noel  writes:

I've tried initdb directly:
initdb -D /zdb/pgsql/data
...and still seem to wind up with the error:
creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/00010001" (log file 0,
segment 1): No such file or directory






You could get more information by using the --noclean switch to prevent
removal of the datadir after failure, and then having a look at the
debris.  Is there a pg_xlog subdirectory inside /zdb/pgsql/data, and if
so what permissions has it got?  Perhaps it is not a directory, but a
symlink to somewhere else?


Always learning, did not know --noclean existed. That is handy. Thanks.



regards, tom lane





--
Adrian Klaver
adrian.kla...@gmail.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] JDBC to load UTF8@psql to latin1@mysql

[Paul Jungwirth]

> The JDBC drivers will handle all the conversion.
> Do NOT manually convert the data.

Yeah, I agree this is the right answer here, since you're using JDBC. By
the time you get a String from the MySQL driver, it's already in Java's
2-bytes-per-char format. And the Postgres driver will deal with the
encoding on the output side. So the code I provided won't help you. I'm
afraid I don't know about Mybatis, but if it's built on JDBC I'd think
you've just got a configuration problem with what encoding the client
expects at either end.

Paul


-- 
_
Pulchritudo splendor veritatis.

Re: [GENERAL] initdb error

[Amitabh Kant]

On Thu, Dec 13, 2012 at 10:05 PM, Tom Lane  wrote:

> David Noel  writes:
> > I've tried initdb directly:
> > initdb -D /zdb/pgsql/data
> > ...and still seem to wind up with the error:
> > creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
> > could not open file "pg_xlog/00010001" (log file 0,
> > segment 1): No such file or directory
>
> Hm, that eliminates my first theory that the rc.d script is using
> initdb's --xlogdir switch to try to put pg_xlog someplace that doesn't
> exist on your system.  But it seems like this must be some variant of
> that.  A simple permissions problem is not very credible, because at
> this point initdb has successfully made the data directory and a bunch
> of files within it already.
>
> You could get more information by using the --noclean switch to prevent
> removal of the datadir after failure, and then having a look at the
> debris.  Is there a pg_xlog subdirectory inside /zdb/pgsql/data, and if
> so what permissions has it got?  Perhaps it is not a directory, but a
> symlink to somewhere else?
>
> regards, tom lane
>
>
Tom

rc.d script  doesn't seem to be using --xlogdir anywhere.  I have uploaded
the script here:
http://pastebin.com/ahAyZ2tP .


Amitabh

Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql

[Thomas Kellerer]

Emi Lu wrote on 13.12.2012 15:00:



Is there a simple way to load UTF8 data in psql to mysql(with latin1
encoding) through JDBC?


All you need to do is to query the source database, then use
ResultSet.getString() to obtain the data and use a PreparedStatement and
PreparedStatement.setString() to insert/update  the data on the target
database.

The JDBC drivers will handle all the conversion.
Do NOT manually convert the data.

getString() and setString() will do everything correctly.


I am not using stmt directly but through Mybatis for all db transactions.

So, this approach will not work.


Then it's a problem of that MyBatis thing.

Thomas





--
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] initdb error

[David Noel]

> You are doing the above as the database user ex:postgres?

confirmed:

ygg:/usr/home/ygg> whoami
pgsql


> The database user has permissions on /zdb/pgsql/data?

confirmed:

ygg:/usr/home/ygg> ll /zdb/
total 3
drwxrwxrwx  3 cvswheel  3 Dec 12 15:33 cvsroot
drwxrwxrwx  2 pgsql  wheel  2 Dec 13 10:17 pgsql


-- 
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] initdb error

[David Noel]

On 12/13/12, Tom Lane  wrote:
> David Noel  writes:
>> I've tried initdb directly:
>> initdb -D /zdb/pgsql/data
>> ...and still seem to wind up with the error:
>> creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
>> could not open file "pg_xlog/00010001" (log file 0,
>> segment 1): No such file or directory
>
> Hm, that eliminates my first theory that the rc.d script is using
> initdb's --xlogdir switch to try to put pg_xlog someplace that doesn't
> exist on your system.  But it seems like this must be some variant of
> that.  A simple permissions problem is not very credible, because at
> this point initdb has successfully made the data directory and a bunch
> of files within it already.
>
> You could get more information by using the --noclean switch to prevent
> removal of the datadir after failure, and then having a look at the
> debris.  Is there a pg_xlog subdirectory inside /zdb/pgsql/data, and if
> so what permissions has it got?  Perhaps it is not a directory, but a
> symlink to somewhere else?
>
>   regards, tom lane
>

ygg:/usr/home/ygg> initdb -D /zdb/pgsql/data --noclean
...
creating template1 database in /zdb/pgsql/data/base/1 ... FATAL:
could not open file "pg_xlog/00010001" (log file 0,
segment 1): No such file or directory
child process exited with exit code 1
initdb: data directory "/zdb/pgsql/data" not removed at user's request

ygg:/usr/home/ygg> ll /zdb/pgsql/
total 2
drwx--  14 pgsql  wheel  18 Dec 13 12:42 data

ygg:/usr/home/ygg> ll /zdb/pgsql/data/
total 53
-rw---  1 pgsql  wheel  4 Dec 13 12:42 PG_VERSION
drwx--  3 pgsql  wheel  3 Dec 13 12:42 base
drwx--  2 pgsql  wheel  2 Dec 13 12:42 global
drwx--  2 pgsql  wheel  2 Dec 13 12:42 pg_clog
-rw---  1 pgsql  wheel   4467 Dec 13 12:42 pg_hba.conf
-rw---  1 pgsql  wheel   1636 Dec 13 12:42 pg_ident.conf
drwx--  4 pgsql  wheel  4 Dec 13 12:42 pg_multixact
drwx--  2 pgsql  wheel  3 Dec 13 12:42 pg_notify
drwx--  2 pgsql  wheel  2 Dec 13 12:42 pg_serial
drwx--  2 pgsql  wheel  2 Dec 13 12:42 pg_snapshots
drwx--  2 pgsql  wheel  2 Dec 13 12:42 pg_stat_tmp
drwx--  2 pgsql  wheel  2 Dec 13 12:42 pg_subtrans
drwx--  2 pgsql  wheel  2 Dec 13 12:42 pg_tblspc
drwx--  2 pgsql  wheel  2 Dec 13 12:42 pg_twophase
drwx--  3 pgsql  wheel  3 Dec 13 12:42 pg_xlog
-rw---  1 pgsql  wheel  19713 Dec 13 12:42 postgresql.conf

ygg:/usr/home/ygg> ll /zdb/pgsql/data/pg_xlog/
total 2
drwx--  2 pgsql  wheel  2 Dec 13 12:42 archive_status


-- 
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] initdb error

[Adrian Klaver]

On 12/13/2012 10:42 AM, David Noel wrote:

You are doing the above as the database user ex:postgres?


confirmed:

ygg:/usr/home/ygg> whoami
pgsql



The database user has permissions on /zdb/pgsql/data?


confirmed:

ygg:/usr/home/ygg> ll /zdb/
total 3
drwxrwxrwx  3 cvswheel  3 Dec 12 15:33 cvsroot
drwxrwxrwx  2 pgsql  wheel  2 Dec 13 10:17 pgsql




Hmmm. Saw the subsequent post also.

More questions:(
The whoami above shows running from ygg home directory.
How did you become pgsql?
Does the pgsql user have a login account you could use?
Where is /zdb ?



--
Adrian Klaver
adrian.kla...@gmail.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] initdb error

[Adrian Klaver]

On 12/13/2012 10:47 AM, David Noel wrote:

On 12/13/12, Tom Lane  wrote:



ygg:/usr/home/ygg> ll /zdb/pgsql/data/pg_xlog/
total 2
drwx--  2 pgsql  wheel  2 Dec 13 12:42 archive_status



Different train of thought, away from permissions.

How was Postgres installed?

Where there any errors during the install process?--

Is there another version of Postgres on this machine?

Adrian Klaver
adrian.kla...@gmail.com


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

[GENERAL] pg_restore error with out of memory

[AI Rumman]

I am going to restore a 6 Gb database in my development machine which is
running on Centos 5.6 with memory 1 GB.
During restoration I got error  as follows:

LOG:  checkpoints are occurring too frequently (22 seconds apart)
HINT:  Consider increasing the configuration parameter
"checkpoint_segments".
pg_restore: out of memory
pg_restore: finished item 8570 TABLE DATA entity
pg_restore: [archiver] worker process failed: exit code 1
[postgres@rumman data]$ ERROR:  invalid input syntax for integer: "U"
CONTEXT:  COPY entity, line 2120568, column version: "U"
STATEMENT:  COPY entity (crmid, smcreatorid, smownerid, modifiedby, setype,
description, createdtime, modifiedtime, viewedtime, status, version,
presence, deleted, owner_type) FROM stdin;

LOG:  could not send data to client: Broken pipe
STATEMENT:  COPY entity (crmid, smcreatorid, smownerid, modifiedby, setype,
description, createdtime, modifiedtime, viewedtime, status, version,
presence, deleted, owner_type) FROM stdin;


The table entity has 2164182 rows.

And description as -
\d entity
  Table "public.entity"
Column|Type |  Modifiers
--+-+--
 crmid| integer | not null
 smcreatorid  | integer | not null default 0
 smownerid| integer | not null default 0
 modifiedby   | integer | not null default 0
 setype   | character varying(30)   | not null
 description  | text|
 createdtime  | timestamp without time zone | not null
 modifiedtime | timestamp without time zone | not null
 viewedtime   | timestamp without time zone |
 status   | character varying(50)   |
 version  | integer | not null default 0
 presence | integer | default 1
 deleted  | integer | not null default 0
 owner_type   | character(1)| not null default 'U'::bpchar
Indexes:
"entity_pkey" PRIMARY KEY, btree (crmid)
"entity_createdtime_idx" btree (createdtime)
"entity_modifiedby_idx" btree (modifiedby)
"entity_modifiedtime_idx" btree (modifiedtime)
"entity_setype_idx" btree (setype) WHERE deleted = 0
"entity_smcreatorid_idx" btree (smcreatorid)
"entity_smownerid_idx" btree (smownerid)
"ftx_enentity_description" gin (to_tsvector('en'::regconfig,
for_fts(description)))
"entity_deleted_idx" btree (deleted)
Referenced by:
TABLE "service" CONSTRAINT "fk_1_service" FOREIGN KEY (serviceid)
REFERENCES entity(crmid) ON DELETE CASCADE
TABLE "servicecontracts" CONSTRAINT "fk_1_servicecontracts" FOREIGN KEY
(servicecontractsid) REFERENCES entity(crmid) ON DELETE CASCADE
TABLE "cc2entity" CONSTRAINT "fk_cc2entityentity" FOREIGN KEY (crm_id)
REFERENCES entity(crmid) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "emails_optout_history" CONSTRAINT "fk_emails_optout_historyid"
FOREIGN KEY (crmid) REFERENCES entity(crmid) ON DELETE CASCADE
TABLE "emails_optout_history" CONSTRAINT
"fk_emails_optout_history_emailid" FOREIGN KEY (emailid) REFERENCES
entity(crmid) ON DELETE CASCADE



I set postgresql.conf as -
shared_memory = 128 MB
maintenance_work_mem = 300 MB
checkpoint_segment = 10  # as the disk space is limited
fsync=off
autocommit=off


The backup was takes at Postgresql 9.2.3 and I am going to restore at
Postrgesql 9.2.1.

During error my OS status:
free -m
 total   used   free sharedbuffers cached
Mem:  1024975 48  0  3857
-/+ buffers/cache:114909
Swap: 1027  0   1027


Please let me know what could be the actual cause of the error.

Thanks.

[GENERAL] XML Schema for PostgreSQL database

[Edson Richter]

Has anyone created a XML Schema that would represent PostgreSQL database 
with all (or at least, major) structures?


Thanks,

Edson



--
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?

[John R Pierce]

On 12/13/2012 5:32 AM, seil...@so-net.net.tw wrote:

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);


Which row is the last row?   relations are sets, not ordered lists.


--
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?

[Edson Richter]

Em 13/12/2012 18:22, John R Pierce escreveu:

On 12/13/2012 5:32 AM, seil...@so-net.net.tw wrote:
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);


Which row is the last row?   relations are sets, not ordered lists.



Last row is not the row that remains, no matter the order?

Edson



--
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] JDBC to load UTF8@psql to latin1@mysql

[Tom Lane]

Paul Jungwirth  writes:
> Yeah, I agree this is the right answer here, since you're using JDBC. By
> the time you get a String from the MySQL driver, it's already in Java's
> 2-bytes-per-char format. And the Postgres driver will deal with the
> encoding on the output side. So the code I provided won't help you. I'm
> afraid I don't know about Mybatis, but if it's built on JDBC I'd think
> you've just got a configuration problem with what encoding the client
> expects at either end.

I was wondering if the problem wasn't lots simpler than that.  Is the
character the OP is trying to convert actually part of LATIN1?

regards, tom lane


-- 
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?

[Chris Angelico]

On Fri, Dec 14, 2012 at 7:22 AM, John R Pierce  wrote:
> On 12/13/2012 5:32 AM, seil...@so-net.net.tw wrote:
>>
>> 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);
>
>
> Which row is the last row?   relations are sets, not ordered lists.

My understanding of the OP is that this is a constraint whereby there
must always be at least one remaining row for a given value of c1.
That is to say, you may delete any row from t1 as long as it's not the
last row (temporally, not sequentially) with that c1.

ChrisA


-- 
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] initdb error

[David Noel]

On 12/13/12, Adrian Klaver  wrote:
> On 12/13/2012 10:47 AM, David Noel wrote:
>> On 12/13/12, Tom Lane  wrote:
>
>> ygg:/usr/home/ygg> ll /zdb/pgsql/data/pg_xlog/
>> total 2
>> drwx--  2 pgsql  wheel  2 Dec 13 12:42 archive_status
>>
>
> Different train of thought, away from permissions.
>
> How was Postgres installed?
>
> Where there any errors during the install process?--
>
> Is there another version of Postgres on this machine?
>
> Adrian Klaver
> adrian.kla...@gmail.com
>

I'd logged in as ygg via `su ygg`.

/zdb is a zfs volume I've created for cvs and postgres.

Postgres was installed via `make install` and there were no errors
during the install process.

I could try deinstalling and installing it again... maybe that will
fix something.


-- 
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] initdb error

[David Noel]

Unfortunately no luck there. Still stick with the same error.

On 12/13/12, David Noel  wrote:
> On 12/13/12, Adrian Klaver  wrote:
>> On 12/13/2012 10:47 AM, David Noel wrote:
>>> On 12/13/12, Tom Lane  wrote:
>>
>>> ygg:/usr/home/ygg> ll /zdb/pgsql/data/pg_xlog/
>>> total 2
>>> drwx--  2 pgsql  wheel  2 Dec 13 12:42 archive_status
>>>
>>
>> Different train of thought, away from permissions.
>>
>> How was Postgres installed?
>>
>> Where there any errors during the install process?--
>>
>> Is there another version of Postgres on this machine?
>>
>> Adrian Klaver
>> adrian.kla...@gmail.com
>>
>
> I'd logged in as ygg via `su ygg`.
>
> /zdb is a zfs volume I've created for cvs and postgres.
>
> Postgres was installed via `make install` and there were no errors
> during the install process.
>
> I could try deinstalling and installing it again... maybe that will
> fix something.
>


-- 
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] initdb error

[Tom Lane]

David Noel  writes:
> On 12/13/12, Tom Lane  wrote:
>> You could get more information by using the --noclean switch to prevent
>> removal of the datadir after failure, and then having a look at the
>> debris.  Is there a pg_xlog subdirectory inside /zdb/pgsql/data, and if
>> so what permissions has it got?  Perhaps it is not a directory, but a
>> symlink to somewhere else?

[ nothing obviously wrong there... ]

Well, it looks like there are no easy answers here, so we'll have to
start digging.  Is this a purely stock version of Postgres?  Which
release exactly?  Where did you get it from, or if you built it
yourself, what configuration options did you use?  What's the platform
exactly?

What I'd suggest as a next step is to use strace or local equivalent on
the initdb run, to capture a log of everything it does with pg_xlog.
(And everything else too --- the log is likely to be bulky, so please
don't post it here.  You could send it to me off-list if it makes no
sense to you.)

regards, tom lane


-- 
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] initdb error

[Tom Lane]

David Noel  writes:
> /zdb is a zfs volume I've created for cvs and postgres.

zfs eh?  What happens if you point initdb at a non-zfs volume?

(I"m wondering if zfs has issues with the O_DIRECT flag that we'll
probably try to use with pg_xlog files.)

regards, tom lane


-- 
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] initdb error

[David Noel]

On 12/13/12, Tom Lane  wrote:
> David Noel  writes:
>> /zdb is a zfs volume I've created for cvs and postgres.
>
> zfs eh?  What happens if you point initdb at a non-zfs volume?
>
> (I"m wondering if zfs has issues with the O_DIRECT flag that we'll
> probably try to use with pg_xlog files.)

I /boot off of a UFS volume so I created a directory there, chown and
chmod'ed it, then ran initdb again. Same error, unfortunately.


-- 
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] initdb error

[Tom Lane]

David Noel  writes:
> On 12/13/12, Tom Lane  wrote:
>> (I"m wondering if zfs has issues with the O_DIRECT flag that we'll
>> probably try to use with pg_xlog files.)

> I /boot off of a UFS volume so I created a directory there, chown and
> chmod'ed it, then ran initdb again. Same error, unfortunately.

Hm.  So far as I can find from googling, ZFS doesn't support O_DIRECT,
or if it does the support is very new.  However, if that were the
problem then you ought to be seeing EINVAL or similar, not ENOENT;
and what's more the file should have gotten created before we'd try to
open it with O_DIRECT, so it should have been there after --noclean.

So I'm still baffled.  Back to wondering if strace will show anything
interesting.

regards, tom lane


-- 
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] initdb error

[Adrian Klaver]

On 12/13/2012 01:40 PM, David Noel wrote:

On 12/13/12, Tom Lane  wrote:

David Noel  writes:

/zdb is a zfs volume I've created for cvs and postgres.


zfs eh?  What happens if you point initdb at a non-zfs volume?

(I"m wondering if zfs has issues with the O_DIRECT flag that we'll
probably try to use with pg_xlog files.)


I /boot off of a UFS volume so I created a directory there, chown and
chmod'ed it, then ran initdb again. Same error, unfortunately.


Well this has definitely moved up a support level and past anything I 
know about.
For the record what version of FreeBSD are you running in case someone 
is searching the archives?








--
Adrian Klaver
adrian.kla...@gmail.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] XML Schema for PostgreSQL database

[Merlin Moncure]

On Thu, Dec 13, 2012 at 1:54 PM, Edson Richter  wrote:
> Has anyone created a XML Schema that would represent PostgreSQL database
> with all (or at least, major) structures?

no -- furthermore, why would you want to?  what would be the consumer
of this 'schema'?

merlin


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

[GENERAL] Read recover rows

[Alejandro Carrillo]

Hi,

1) Anybody knows how to create a table using a table 
file? It isn't a fdw, is a file that compose the table in postgresql and
 get with the pg_relation_filepath function. Ex:
 
 select pg_relation_filepath('pg_proc');
 
2) Anybody knows a JDBC or a multiplatform code that let read the delete rows 
of a table without writing of a table file?

Thanks

Re: [GENERAL] execution plan is wrong, or the query ?

[Alex Burkoff]

Upgrade to 9.2.2 totally fixed the issue. Thanks again!


From: Tom Lane [t...@sss.pgh.pa.us]
Sent: Tuesday, December 11, 2012 9:10 AM
To: Alex Burkoff
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] execution plan is wrong, or the query ?

Alex Burkoff  writes:
> I think I am just looking for an opinion on fundamentals. Can WHERE clause 
> impact an OUTER JOIN ?

Sure.  I noticed for instance that your query had some outer joins that
were simplified to plain joins as a result of strict WHERE clauses above
them.  That's not incorrect.

> What I am seeing is that on 9.2 rows from the joined table are restricted 
> prior to joining them with the rest
> of the tables, and that leads to incorrect results. I have noticed such 
> behaviour only when CASE is used
> in the WHERE clause - straight text comparison does produce correct results.

Oh?  Hm, I wonder whether you are needing this fix:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=72a4231f0c80f213a4fa75356dc3c6b7c7419059
Although that bug exists in some form back to 7.4, 9.2 had a more
obvious form of the disease, which is what led to its diagnosis.
If you're not running 9.2.2, give that a try and see if it's better.

regards, tom lane


-- 
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] Read recover rows

[Adrian Klaver]

On 12/13/2012 02:23 PM, Alejandro Carrillo wrote:

Hi,

1) Anybody knows how to create a table using a table file? It isn't a
fdw, is a file that compose the table in postgresql and get with the
pg_relation_filepath function. Ex:

  select pg_relation_filepath('pg_proc');


Not sure what you are asking. The above just returns a file path to the 
on disk representation of the table.


Are you looking for

CREATE TABLE AS

http://www.postgresql.org/docs/9.2/interactive/sql-createtableas.html

OR

CREATE TABLE LIKE

http://www.postgresql.org/docs/9.2/interactive/sql-createtable.html

OR

Do want to create a table from a script?



2) Anybody knows a JDBC or a multiplatform code that let read the delete
rows of a table without writing of a table file?


Not sure if that is possible, someone else may have a better answer.



Thanks



--
Adrian Klaver
adrian.kla...@gmail.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] Read recover rows

[Alejandro Carrillo]

Hi,

1) Isn't a script. The file is a table file get using the function 
pg_relation_filepath:
select pg_relation_filepath('pg_proc');
2) :( help!




>
> De: Adrian Klaver 
>Para: Alejandro Carrillo  
>CC: "pgsql-general@postgresql.org"  
>Enviado: Jueves 13 de diciembre de 2012 17:59
>Asunto: Re: [GENERAL] Read recover rows
> 
>On 12/13/2012 02:23 PM, Alejandro Carrillo wrote:
>> Hi,
>>
>> 1) Anybody knows how to create a table using a table file? It isn't a
>> fdw, is a file that compose the table in postgresql and get with the
>> pg_relation_filepath function. Ex:
>>
>>   select pg_relation_filepath('pg_proc');
>
>Not sure what you are asking. The above just returns a file path to the 
>on disk representation of the table.
>
>Are you looking for
>
>CREATE TABLE AS
>
>http://www.postgresql.org/docs/9.2/interactive/sql-createtableas.html
>
>OR
>
>CREATE TABLE LIKE
>
>http://www.postgresql.org/docs/9.2/interactive/sql-createtable.html
>
>OR
>
>Do want to create a table from a script?
>
>>
>> 2) Anybody knows a JDBC or a multiplatform code that let read the delete
>> rows of a table without writing of a table file?
>
>Not sure if that is possible, someone else may have a better answer.
>
>>
>> Thanks
>
>
>-- 
>Adrian Klaver
>adrian.kla...@gmail.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] Read recover rows

[Adrian Klaver]

On 12/13/2012 03:14 PM, Alejandro Carrillo wrote:

Hi,

1) Isn't a script. The file is a table file get using the function
pg_relation_filepath:
select pg_relation_filepath('pg_proc');


The above does NOT return a file it just returns the file path name. 
Postgres stores it tables(relations) with numbers. This function just 
helps which number is associated with a table.



2) :( help!


Still not sure what you are trying to do:)

Wild guess, pg_proc was deleted and you want to recover it?


--
Adrian Klaver
adrian.kla...@gmail.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] Read recover rows

[Alejandro Carrillo]

I have a file CALLED 11649 in the path: %PG_DATA%\base\11912
This file is a table data in postgresql. Ex: pg_proc.
Now I copied this file, renamed it and I want to connect this file to another 
new table with the same structure and data.
How I can do this?




>
> De: Adrian Klaver 
>Para: Alejandro Carrillo  
>CC: "pgsql-general@postgresql.org"  
>Enviado: Jueves 13 de diciembre de 2012 18:23
>Asunto: Re: [GENERAL] Read recover rows
> 
>On 12/13/2012 03:14 PM, Alejandro Carrillo wrote:
>> Hi,
>> 
>> 1) Isn't a script. The file is a table file get using the function
>> pg_relation_filepath:
>> select pg_relation_filepath('pg_proc');
>
>The above does NOT return a file it just returns the file path name. Postgres 
>stores it tables(relations) with numbers. This function just helps which 
>number is associated with a table.
>
>> 2) :( help!
>
>Still not sure what you are trying to do:)
>
>Wild guess, pg_proc was deleted and you want to recover it?
>
>
>-- Adrian Klaver
>adrian.kla...@gmail.com
>
>
>

Re: [GENERAL] Read recover rows

[Adrian Klaver]

On 12/13/2012 03:30 PM, Alejandro Carrillo wrote:

I have a file CALLED 11649 in the path: %PG_DATA%\base\11912
This file is a table data in postgresql. Ex: pg_proc.
Now I copied this file, renamed it and I want to connect this file to
another new table with the same structure and data.
How I can do this?


As far as I know you cannot. If you want to create a new table from an 
existing table you will need to use the SQL commands I mentioned 
previously. This assumes the original table exists. Are you trying to 
recover a table by copying in a table from somewhere else?








--
Adrian Klaver
adrian.kla...@gmail.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] Read recover rows

[Alejandro Carrillo]

Are you trying to recover a table by copying in a table from somewhere else? 
Yes because I can't modify the original file

>
> De: Adrian Klaver 
>Para: Alejandro Carrillo  
>CC: "pgsql-general@postgresql.org"  
>Enviado: Jueves 13 de diciembre de 2012 18:39
>Asunto: Re: [GENERAL] Read recover rows
> 
>On 12/13/2012 03:30 PM, Alejandro Carrillo wrote:
>> I have a file CALLED 11649 in the path: %PG_DATA%\base\11912
>> This file is a table data in postgresql. Ex: pg_proc.
>> Now I copied this file, renamed it and I want to connect this file to
>> another new table with the same structure and data.
>> How I can do this?
>
>As far as I know you cannot. If you want to create a new table from an 
>existing table you will need to use the SQL commands I mentioned previously. 
>This assumes the original table exists. Are you trying to recover a table by 
>copying in a table from somewhere else?
>
>> 
>> 
>
>
>-- Adrian Klaver
>adrian.kla...@gmail.com
>
>
>

Re: [GENERAL] XML Schema for PostgreSQL database

[Edson Richter]

Em 13/12/2012 20:10, Merlin Moncure escreveu:

On Thu, Dec 13, 2012 at 1:54 PM, Edson Richter  wrote:

Has anyone created a XML Schema that would represent PostgreSQL database
with all (or at least, major) structures?

no -- furthermore, why would you want to?  what would be the consumer
of this 'schema'?

merlin




I was wondering to create a tool for diagramming and database forward 
engineering.


There are already few tools around.

If you know a good diagramming tool able to database diff and forward 
engineering (with "ALTER ...", not "DROP and CREATE"), I would like to 
know (by today I do use one commercial tool that is feature incomplete: 
DbWrench).


Among others, I've considered also:
- Sybase PowerDesigner: too expensive, does not support PostgreSQL 
9.1/9.2, so is not appropriate.
- ERWin: too expensive, and doesn't have proper support for PostgreSQL 
9.1/9.2.
- NaviCat: is feature extensive, but they don't have real change scripts 
(are drop/create).
- ModelRight: it's "change script" is not change at all (is just another 
drop/create tool).

- TORA and other open source tools are really incomplete.
- TOAD is too confuse for simple day-by-day work.

Most of these tools or doesn't support PostgreSQL features (are too 
generic), or doesn't do real forward engineer (are only able to 
drop/create objects, not alter them), or cannot deal with partial 
diagrams (I can't deal with only one diagram with hundred of tables at 
once).


Thanks for your help,

Edson




--
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] Read recover rows

[Adrian Klaver]

On 12/13/2012 03:41 PM, Alejandro Carrillo wrote:

Are you trying to recover a table by copying in a table from somewhere
else? Yes because I can't modify the original file


You will not be able to work with the disk file directly, you will need 
to go through the database.


Have you tried pg_dump:

http://www.postgresql.org/docs/9.2/interactive/app-pgdump.html

Something like:

pg_dump -t some_table -f some_table.sql -U some_user  database_name

Where the dummy names are replaced with the table/database you want.

This will create a plain text file. If you need to change the name you 
could do find and replace on the table name.



This assumes you are trying to move a user created table not a system 
table.

Is that the case?


--
Adrian Klaver
adrian.kla...@gmail.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] pg_restore error with out of memory

[Kevin Grittner]

AI Rumman wrote:

I am going to restore a 6 Gb database in my development machine
which is running on Centos 5.6 with memory 1 GB.

> pg_restore: out of memory
> pg_restore: finished item 8570 TABLE DATA entity
> pg_restore: [archiver] worker process failed: exit code 1

> I set postgresql.conf as -
> shared_memory = 128 MB
> maintenance_work_mem = 300 MB

> During error my OS status:
> free -m
>             total used free shared buffers cached
>         Mem: 1024  975   48      0       3    857
> -/+ buffers/cache: 114  909
>        Swap: 1027    0 1027
> 
> Please let me know what could be the actual cause of the error.

You have 1024 MB total RAM.
You seem to be using 114 MB of that before starting PostgreSQL.
You have PostgreSQL configured to use 128 MB of shared buffers,
which is only part of its shared memory.
You have configured 300 MB per maintenance_work_mem allocation.
There can be several of these at one time.
You are running pg_restore, which needs to use memory to interpret
the map of the dump and dependencies among objects.

You are using more memory than you have.

If you really need to run PostgreSQL on a machine with 1GB of
memory, you need to use a configuration much closer to the default.

Don't expect performance to be the same as on a larger server.

-Kevin


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

[GENERAL] Monitoring streaming replication from standby on Windows

[Yamen LA]

Hello,

I would like to know how to check the status of the streaming replication from 
standby server on Windows. Apparently from the master I can use the pg table 
"pg_stat_replication". This table is, however, empty on the standby since it 
contains information about WAL sender processes and not WAL receiver. 
pg_last_xlog_replay_location and pg_last_xlog_receive_location also continue to 
be valid even when the streaming replication is down, so they don't help in 
this case.
>From online tutorials and PostgreSQL wiki the only way I found is by checking 
>the running processes for wal sender and wal receiver using ps command on Unix 
>systems. The problem is that on Windows, all those processes carry the same 
>name, postgresql.exe.

I suppose there should be some parameter to get the db engine as it realizes 
when the streaming replication is down and it logs that in pg_log files, but I 
can't seem to find such a parameter.

Thank you for your help.

-Yamen
  

Re: [GENERAL] Monitoring streaming replication from standby on Windows

[Karl Denninger]

On 12/13/2012 7:36 PM, Yamen LA wrote:
> Hello,
>
> I would like to know how to check the status of the streaming
> replication from standby server on Windows. Apparently from the master
> I can use the pg table "pg_stat_replication". This table is, however,
> empty on the standby since it contains information about WAL sender
> processes and not WAL receiver. pg_last_xlog_replay_location and
> pg_last_xlog_receive_location also continue to be valid even when the
> streaming replication is down, so they don't help in this case.
> From online tutorials and PostgreSQL wiki the only way I found is by
> checking the running processes for wal sender and wal receiver using
> ps command on Unix systems. The problem is that on Windows, all those
> processes carry the same name, postgresql.exe.
>
> I suppose there should be some parameter to get the db engine as it
> realizes when the streaming replication is down and it logs that in
> pg_log files, but I can't seem to find such a parameter.
>
> Thank you for your help.
>
> -Yamen
What are you trying to determine?

If it's whether the replication is caught up, I have a small "C" program
that will do that and have posted it before (I can do that again if
you'd like.)

If it's whether it's "up", that's a bit more complex, since you have to
define "up." 

For most purposes determining that the offset between the two is less
than some value at which you alarm is sufficient, and if you then alarm
if you can't reach the master and slave hosts, you then know if the
machines are "up" from a standpoint of reachability on the network as well.

-- 
-- Karl Denninger
/The Market Ticker ®/ 
Cuda Systems LLC

Re: [GENERAL] initdb error

[Amitabh Kant]

On Fri, Dec 14, 2012 at 3:10 AM, David Noel  wrote:

> On 12/13/12, Tom Lane  wrote:
> > David Noel  writes:
> >> /zdb is a zfs volume I've created for cvs and postgres.
> >
> > zfs eh?  What happens if you point initdb at a non-zfs volume?
> >
> > (I"m wondering if zfs has issues with the O_DIRECT flag that we'll
> > probably try to use with pg_xlog files.)
>
> I /boot off of a UFS volume so I created a directory there, chown and
> chmod'ed it, then ran initdb again. Same error, unfortunately.
>
>
David

Did you use ports to install postgresql? What is the version of postgresql
and freebsd you are using? I am getting a different output while running
the initdb command through the rc script, and it's not using the -D path to
initialize the cluster, it falls back to the default location
/usr/local/pgsql/data .  Setting postgresql_data="/usr/local/pgsql1/data"
in /etc/rc.conf makes it initialize at the right location .  I did not had
to mess with an permissions and the rc commands were all run as root. The
above was tested on FreeBSD 9.1 with Postgresql 9.2 on UFS.

On FreeBSD 8.1 with Postgresql 9.1 on UFS  installed though ports, it was
the same story, except that I had to change ownership to user pgsql for the
/usr/local/pgsql1 directory

Amitabh

Re: [GENERAL] Read recover rows

[Alvaro Herrera]

Alejandro Carrillo escribió:
> Hi,
> 
> 1) Anybody knows how to create a table using a table 
> file? It isn't a fdw, is a file that compose the table in postgresql and
>  get with the pg_relation_filepath function. Ex:
>  
>  select pg_relation_filepath('pg_proc');

Make sure the server is down and replace a table's file with the file
you have.  You can just create a dummy empty table with exactly the same
row type as the one that had the table the file was for; you need to
recreate dropped columns as well.

> 2) Anybody knows a JDBC or a multiplatform code that let read the delete rows 
> of a table without writing of a table file?

You already tried pg_dirtyread, I imagine, after I suggested it to you
in the spanish list?  You can use it through JDBC.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


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

[GENERAL] PostgreSQL contrib 9.2.x

[a...@hsk.hk]

Hi,

I am using Ubuntu 12.04, I have used installer to install PostgreSQL 9.2.1, the 
contrib modules are not installed together with the core.  

I tried to run: 

sudo apt-get install postgresql-contrib
The following packages were automatically installed and are no longer 
required:
libnet-daemon-perl linux-headers-3.2.0-33 libdbi-perl libdbd-mysql-perl
libplrpc-perl linux-headers-3.2.0-33-generic
Use 'apt-get autoremove' to remove them.
The following extra packages will be installed:
libossp-uuid16 libpq5 postgresql-9.1 postgresql-client-9.1
postgresql-client-common postgresql-common postgresql-contrib-9.1
Suggested packages:
uuid oidentd ident-server locales-all postgresql-doc-9.1 libdbd-pg-perl
The following NEW packages will be installed:
libossp-uuid16 libpq5 postgresql-9.1 postgresql-client-9.1
postgresql-client-common postgresql-common postgresql-contrib
postgresql-contrib-9.1
0 upgraded, 8 newly installed, 0 to remove and 4 not upgraded.
Need to get 5,995 kB of archives.
After this operation, 17.6 MB of additional disk space will be used.

I could see that it would install older PostgreSQL 9.1 and 
postgresql-contrib-9.1.  As I already have 9.2.1 and do not want to have older 
version 9.1 in parallel, I aborted the apt install. 

How can I get pure postgresql-contrib for Postgresql 9.2.x? 

Thanks
ac