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
Re: [GENERAL] Trouble with PQnotifies()
seil...@so-net.net.tw wrote: 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. .. 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(). I was looking at it carefully, and was about to ask about the connection- in particular whether it was shared across threads etc. Glad you've found the issue, I've been caught by something very similar using list/notify on Lazarus/FPC where you can end up with several handles only one of which is reliable. 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. But at least it demonstrates that somebody's using that facility. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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
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? Any help is always appreciated. FreeBSD 8.3-RELEASE-p5. (I wonder if upgrading to 9.1 would do anything..) -- 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
Did you use ports to install postgresql? Yes What is the version of postgresql and freebsd you are using? postgresql client and server v. 9.2.2. If all else fails I could try downgrading to a previous version of postgres. FreeBSD 8.3-RELEASE-p5 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. I didn't have any luck with the rc script but I was able to use it to get a ktrace dump as root (ktrace as user pgsql doesn't seem to work). So hopefully that will show 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
On Fri, Dec 14, 2012 at 4:28 PM, David Noel david.i.n...@gmail.com wrote: Did you use ports to install postgresql? Yes What is the version of postgresql and freebsd you are using? postgresql client and server v. 9.2.2. If all else fails I could try downgrading to a previous version of postgres. FreeBSD 8.3-RELEASE-p5 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. I didn't have any luck with the rc script but I was able to use it to get a ktrace dump as root (ktrace as user pgsql doesn't seem to work). So hopefully that will show something(!) David If it helps, my versions were FreeBSD 8.1 + PG 9.0.5 using UFS on a dedicated server and FreeBSD 9.1 (available on FTP sites but not announced yet) + PG 9.2.2 using UFS on a virtual box instance. Looks like ZFS is the only major variable here. Amitabh
Re: [GENERAL] PostgreSQL contrib 9.2.x
Hi, On 14 December 2012 17:56, a...@hsk.hk a...@hsk.hk wrote: 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? You need PostreSQL PPA: sudo apt-get update sudo apt-get install python-software-properties sudo add-apt-repository ppa:pitti/postgresql sudo apt-get install postgresql-contrib-9.2 -- Ondrej Ivanic (http://www.linkedin.com/in/ondrejivanic) -- 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] PostgreSQL contrib 9.2.x
Hi, got it installed, thanks On 14 Dec 2012, at 7:36 PM, Ondrej Ivanič wrote: Hi, On 14 December 2012 17:56, a...@hsk.hk a...@hsk.hk wrote: 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? You need PostreSQL PPA: sudo apt-get update sudo apt-get install python-software-properties sudo add-apt-repository ppa:pitti/postgresql sudo apt-get install postgresql-contrib-9.2 -- Ondrej Ivanic (http://www.linkedin.com/in/ondrejivanic) -- 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
Re: [GENERAL] initdb error
On 12/13/12, David Noel david.i.n...@gmail.com 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? -David Interestingly, I have a second--virtually identical--server that I just tried initdb on. FreeBSD 8.3-RELEASE-p5, postgresql-server-9.2.2. Exact same FATAL: could not open file pg_xlog error. So it is reproducible. -- 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] Monitoring streaming replication from standby on Windows
Thank you Karl. I am trying to determine on the slave itself whether streaming replication (i.e. WAL receiver process) is active or not, similar to checking pg_stat_replication on the master. In fact, this is part of a larger module I am building to control the databases and automate failovers. As for monitoring the offset between the two, what is a reasonable value for the differences between last xlog sent, received and replayed? -Yamen Date: Thu, 13 Dec 2012 19:43:53 -0600 From: k...@denninger.net To: iya...@live.com CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] Monitoring streaming replication from standby on Windows 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] JDBC to load UTF8@psql to latin1@mysql
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.
From: Tom lane
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?
First, the data loading is from psql(unix) to mysql(Unix). Second, DB
transactions are through JAVA+MyBatis.
Steps:
(1) Read utf8.data@psql from psql.xml into java.ArrayListBean
(2) For each list.rec, save into mysql@latin through mysql.xml
Tried jdbc:mysql://.../mysql_db?...unicode...encoding...=ISO... No.
This does not work.
For now, through the following method, all letters are correctly
transformed except È.
What does OP stand for?
Emi
--
public static String utf8_to_latin1(String str)
throws Exception
{
try
{
if(str.indexOf(È)=0)
{
str = str.replaceAll(È, E);
}
byte[] convertStringToByte = str.getBytes(UTF-8);
str = new String(convertStringToByte, ISO-8859-1);
return str;
}catch(Exception e)
{
log.error(utf8_to_latin1 Error: + e.getMessage());
log.error(e);
throw e;
}
}
--
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
On Thu, Dec 13, 2012 at 5:52 PM, Edson Richter edsonrich...@hotmail.com wrote: Em 13/12/2012 20:10, Merlin Moncure escreveu: On Thu, Dec 13, 2012 at 1:54 PM, Edson Richter edsonrich...@hotmail.com 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). Years ago I decided that the only way to do forward engineering was to capture the changes I make to development databases in scripts and to manually apply those scripts for release management. This process works and like you I've found the various commercial tools to have various weaknesses. So for forward engineering I say: quit using tools and write scripts. I'm also like you amazed how poor the various database diagramming tools are -- they all suck. Case Studio used to be pretty good back in the day but I wouldn't recommend it today. My personal take on ERD/diagramming is that: *) diagram generation should be automatic and useful *) human input to adjust the layout should not be required (every time I move the stupid boxes and straighten the stupid lines I feel like I'm finger painting) *) diagram tool should follow database changes and adjust the diagram accordingly *) diagram output should be standard html (only) without requiring tool to log in and adjust diagram I've come around to the point of view that this is an unfilled niche in the industry. Furthermore, as long as scope is kept reasonably down, this is not a very difficult project. So I've decided (along with Atri) to give it a shot. Iniitial plans is to do plain html dumps directly out of the database and use GraphVis to document dependency flow. 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] Problem starting PG-9.2 on non-default port
I wish to run an instance of PG-9.2 concurrently with our production version (9.1). I have changed the listening port number. listen_addresses = 'pgsql-dbms.hamilton.harte-lyne.ca, localhost' #port = 5432# (change requires restart) port = 5433 However, I am getting this error when I try to start the new version while the old one is still running. Is there something else I must do to accomplish this? Why is the lock file using the default port number for this instance? cat ../pgstartup.log 2012-12-14 09:13:41 EST: LOG: XX000: could not bind IPv4 socket: Address already in use 2012-12-14 09:13:41 EST: HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. 2012-12-14 09:13:41 EST: LOCATION: StreamServerPort, pqcomm.c:438 2012-12-14 09:13:41 EST: WARNING: 01000: could not create listen socket for pgsql-dbms.hamilton.harte-lyne.ca 2012-12-14 09:13:41 EST: LOCATION: PostmasterMain, postmaster.c:892 2012-12-14 09:13:41 EST: FATAL: F0001: lock file /tmp/.s.PGSQL.5432.lock already exists 2012-12-14 09:13:41 EST: HINT: Is another postmaster (PID 1299) using socket file /tmp/.s.PGSQL.5432? 2012-12-14 09:13:41 EST: LOCATION: CreateLockFile, miscinit.c:822 -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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
On 12/14/2012 04:08 AM, David Noel wrote: Interestingly, I have a second--virtually identical--server that I just tried initdb on. FreeBSD 8.3-RELEASE-p5, postgresql-server-9.2.2. Exact same FATAL: could not open file pg_xlog error. So it is reproducible. Does virtually identical extend to architecture, amd64? -- 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
Interestingly, I have a second--virtually identical--server that I just tried initdb on. FreeBSD 8.3-RELEASE-p5, postgresql-server-9.2.2. Exact same FATAL: could not open file pg_xlog error. So it is reproducible. Does virtually identical extend to architecture, amd64? Yes... and hardware. They only vary in the amount of CPU cache--one has twice the other. -- 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] Problem starting PG-9.2 on non-default port
Hi, Is this an RPM installation? If so, you also need to change the port in the init script, too. Regards, Devrim James B. Byrne byrn...@harte-lyne.ca wrote: I wish to run an instance of PG-9.2 concurrently with our production version (9.1). I have changed the listening port number. listen_addresses = 'pgsql-dbms.hamilton.harte-lyne.ca, localhost' #port = 5432 # (change requires restart) port = 5433 However, I am getting this error when I try to start the new version while the old one is still running. Is there something else I must do to accomplish this? Why is the lock file using the default port number for this instance? cat ../pgstartup.log 2012-12-14 09:13:41 EST: LOG: XX000: could not bind IPv4 socket: Address already in use 2012-12-14 09:13:41 EST: HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. 2012-12-14 09:13:41 EST: LOCATION: StreamServerPort, pqcomm.c:438 2012-12-14 09:13:41 EST: WARNING: 01000: could not create listen socket for pgsql-dbms.hamilton.harte-lyne.ca 2012-12-14 09:13:41 EST: LOCATION: PostmasterMain, postmaster.c:892 2012-12-14 09:13:41 EST: FATAL: F0001: lock file /tmp/.s.PGSQL.5432.lock already exists 2012-12-14 09:13:41 EST: HINT: Is another postmaster (PID 1299) using socket file /tmp/.s.PGSQL.5432? 2012-12-14 09:13:41 EST: LOCATION: CreateLockFile, miscinit.c:822 -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent from my Android phone with K-9 Mail. Please excuse my brevity.
Re: [GENERAL] Problem starting PG-9.2 on non-default port
On 12/14/2012 06:26 AM, James B. Byrne wrote: I wish to run an instance of PG-9.2 concurrently with our production version (9.1). I have changed the listening port number. listen_addresses = 'pgsql-dbms.hamilton.harte-lyne.ca, localhost' #port = 5432# (change requires restart) port = 5433 However, I am getting this error when I try to start the new version while the old one is still running. Is there something else I must do to accomplish this? Why is the lock file using the default port number for this instance? Are you sure you are starting the correct server instance? -- 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
On 12/13/2012 06:51 PM, Alvaro Herrera wrote:
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.
So it is possible to slide a file under a table?
How exactly do you do that and keep the OID and filenode references in sync?
--
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] Problem starting PG-9.2 on non-default port
On Fri, December 14, 2012 09:40, Adrian Klaver wrote: On 12/14/2012 06:26 AM, James B. Byrne wrote: I wish to run an instance of PG-9.2 concurrently with our production version (9.1). I have changed the listening port number. listen_addresses = 'pgsql-dbms.hamilton.harte-lyne.ca, localhost' #port = 5432 # (change requires restart) port = 5433 However, I am getting this error when I try to start the new version while the old one is still running. Is there something else I must do to accomplish this? Why is the lock file using the default port number for this instance? Are you sure you are starting the correct server instance? Well, this is what I am doing: service postgresql-9.2 start Starting postgresql-9.2 service: [FAILED] And this is the log file with the error: cat /var/lib/pgsql/9.2/pgstartup.log 2012-12-14 09:43:38 EST: LOG: XX000: could not bind IPv4 socket: Address already in use 2012-12-14 09:43:38 EST: HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. 2012-12-14 09:43:38 EST: LOCATION: StreamServerPort, pqcomm.c:438 2012-12-14 09:43:38 EST: WARNING: 01000: could not create listen socket for pgsql-dbms.hamilton.harte-lyne.ca 2012-12-14 09:43:38 EST: LOCATION: PostmasterMain, postmaster.c:892 2012-12-14 09:43:38 EST: FATAL: F0001: lock file /tmp/.s.PGSQL.5432.lock already exists 2012-12-14 09:43:38 EST: HINT: Is another postmaster (PID 1299) using socket file /tmp/.s.PGSQL.5432? 2012-12-14 09:43:38 EST: LOCATION: CreateLockFile, miscinit.c:822 And this is the running instance: service postgresql-9.1 status postgresql-9.1 (pid 1299) is running... So, I am fairly sure that I am trying to get 9.2 started and failing and not trying to start another instance of 9.1. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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
On 12/14/2012 06:06 AM, Emi Lu wrote: What does OP stand for? Original Poster. Emi -- -- 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] Monitoring streaming replication from standby on Windows
Hello I was just wondering the same. As for the slave, it seems to me that the ps fax output postgres: startup process recovering is at least a quick way to check if the replication client has synced. In a case where e.g. the slave was down for a while and then unsuccessfully waits for WAL files which were long deleted on the master, this line changed to startup process waiting. HTH, -christian- On Fri, 14 Dec 2012 09:52:49 -0400 Yamen LA iya...@live.com wrote: Thank you Karl. I am trying to determine on the slave itself whether streaming replication (i.e. WAL receiver process) is active or not, similar to checking pg_stat_replication on the master. In fact, this is part of a larger module I am building to control the databases and automate failovers. As for monitoring the offset between the two, what is a reasonable value for the differences between last xlog sent, received and replayed? -Yamen Date: Thu, 13 Dec 2012 19:43:53 -0600 From: k...@denninger.net To: iya...@live.com CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] Monitoring streaming replication from standby on Windows 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 -- Network Engineering Design; Content Delivery Platform IP NETCOLOGNE Gesellschaft für Telekommunikation mbH Am Coloneum 9 | 50829 Köln Tel: 0221 -8711 | Fax: 0221 -78711 www.netcologne.de Geschäftsführer: Dr. Hans Konle (Sprecher) Dipl.-Ing. Karl-Heinz Zankel HRB 25580, AG Köln Diese Nachricht (inklusive aller Anhänge) ist vertraulich. Sollten Sie diese Nachricht versehentlich erhalten haben, bitten wir, den Absender (durch Antwort-E-Mail) hiervon unverzüglich zu informieren und die Nachricht zu löschen. Die E-Mail darf in diesem Fall weder vervielfältigt noch in anderer Weise verwendet werden. -- 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] Problem starting PG-9.2 on non-default port
On 14/12/2012 16:37, Devrim Gündüz wrote: Hi, Is this an RPM installation? If so, you also need to change the port in the init script, too. Or if it's rhel/centos/sciencific like linux, you can specify in /etc/sysconfig/pgsql/postgresql-9.2: PGPORT=5433 Regards, Devrim James B. Byrne byrn...@harte-lyne.ca wrote: I wish to run an instance of PG-9.2 concurrently with our production version (9.1). I have changed the listening port number. listen_addresses = 'pgsql-dbms.hamilton.harte-lyne.ca http://pgsql-dbms.hamilton.harte-lyne.ca, localhost' #port = 5432# (change requires restart) port = 5433 However, I am getting this error when I try to start the new version while the old one is still running. Is there something else I must do to accomplish this? Why is the lock file using the default port number for this instance? cat ../pgstartup.log 2012-12-14 09:13:41 EST: LOG: XX000: could not bind IPv4 socket: Address already in use 2012-12-14 09:13:41 EST: HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. 2012-12-14 09:13:41 EST: LOCATION: StreamServerPort, pqcomm.c:438 2012-12-14 09:13:41 EST: WARNING: 01000: could not create listen socket for pgsql-dbms.hamilton.harte-lyne.ca http://pgsql-dbms.hamilton.harte-lyne.ca 2012-12-14 09:13:41 EST: LOCATION: PostmasterMain, postmaster.c:892 2012-12-14 09:13:41 EST: FATAL: F0001: lock file /tmp/.s.PGSQL.5432.lock already exists 2012-12-14 09:13:41 EST: HINT: Is another postmaster (PID 1299) using socket file /tmp/.s.PGSQL.5432? 2012-12-14 09:13:41 EST: LOCATION: CreateLockFile, miscinit.c:822 -- Sent from my Android phone with K-9 Mail. Please excuse my brevity. -- 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] Problem starting PG-9.2 on non-default port
On 12/14/2012 06:48 AM, James B. Byrne wrote: And this is the running instance: service postgresql-9.1 status postgresql-9.1 (pid 1299) is running... So, I am fairly sure that I am trying to get 9.2 started and failing and not trying to start another instance of 9.1. Assuming an RPM install, see Devrims reply. -- 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
On 12/14/2012 09:49 AM, Adrian Klaver wrote:
Original Poster
Thanks. And more info:
Mysql
+--++
| Variable_name| Value |
+--++
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results| latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
+--++
SHOW VARIABLES LIKE character\_set\_database;
+++
| Variable_name | Value |
+++
| character_set_database | latin1 |
+++
Psql everywhere is utf8.
Where could be the problem located?
Also by using the java encoding methods, all characters except È are
transformed correctly.
Thanks alot!
Emi
--
public static String utf8_to_latin1(String str)
throws Exception
{
try
{
if(str.indexOf(È)=0)
{
str = str.replaceAll(È, E);
}
byte[] convertStringToByte = str.getBytes(UTF-8);
str= new String(convertStringToByte,
ISO-8859-1);
return str;
}catch(Exception e)
{
log.error(utf8_to_latin1 Error: + e.getMessage());
log.error(e);
throw e;
}
}
--
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 em...@encs.concordia.ca writes: For now, through the following method, all letters are correctly transformed except È. Meh. That character renders as \310 in your mail, which is not an assigned code in ISO 8859-1. The numerically corresponding Unicode value would be U+0090, which is an unspecified control character. I surmise that your source data is not actually either Unicode or ISO 8859-1, but one of the random extended character sets that Microsoft has loosed upon the world, perhaps windows-1252 http://en.wikipedia.org/wiki/Windows-1252 The conversion code that you're using is quite right to reject the character as not being valid LATIN1. What you need to do is figure out what the data actually is and correct its encoding. It's evidently stored wrong in the UTF8 data, if you believe that this code is a letter. 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
[GENERAL] Implicit casts to array types
I'm trying to create an implicit cast from an unknown type into a text array
type by creating a simple array of size 1. e.g.
create function textarray(unknown)
returns text[]
as 'select ARRAY[$1::text];'
language sql
immutable;
create cast (unknown as text[]) with function textarray(unknown) as
implicit;
However, when I try to use this, the planner doesn't use the implicit cast.
Instead it still tries to cast 'a' directly to a text[] and complains that
it's not formatted as '{a}' (ERROR: array value must start with { or
dimension information)
I added an additional parallel cast from text to text[]:
create function textarray(text)
returns text[]
as 'select ARRAY[$1];'
language sql
immutable;
create cast (text as text[]) with function textarray(text) as implicit;
Now, if I explicitly cast 'a'::text the implicit cast to text[] fires.
However, this doesn't help because I need all the implicit casts to fire
since this is intended to be used by COPY FROM.
I tried adding an implicit cast from unknown to text to try to get
unknown-text-text[], but that didn't work either (same error as first
attempt).
Is there something special about the unknown data type that I'm unaware of?
I don't understand why it worked for text but not for unknown.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Implicit-casts-to-array-types-tp5736582.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Problem starting PG-9.2 on non-default port
On Fri, December 14, 2012 09:52, Adrian Klaver wrote: Assuming an RPM install, see Devrims reply. Might one inquire as to why it is necessary to override the configuration file in the startup script? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Implicit casts to array types
On Fri, Dec 14, 2012 at 9:16 AM, joshua jzuel...@arbormetrix.com wrote:
I'm trying to create an implicit cast from an unknown type into a text array
type by creating a simple array of size 1. e.g.
create function textarray(unknown)
returns text[]
as 'select ARRAY[$1::text];'
language sql
immutable;
create cast (unknown as text[]) with function textarray(unknown) as
implicit;
However, when I try to use this, the planner doesn't use the implicit cast.
Instead it still tries to cast 'a' directly to a text[] and complains that
it's not formatted as '{a}' (ERROR: array value must start with { or
dimension information)
I added an additional parallel cast from text to text[]:
create function textarray(anyelement)
returns text[]
as 'select ARRAY[$1::text];'
language sql
immutable;
(emphasis on 'anyelement')...get rid of the cast. use 'any' type
arguments for polymorphic functions, that is when you want them to
operate over wide range of input types. hacking casts is almost never
a good idea.
merlin
--
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 adrian.kla...@gmail.com writes: On 12/13/2012 06:51 PM, Alvaro Herrera wrote: 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. So it is possible to slide a file under a table? How exactly do you do that and keep the OID and filenode references in sync? The XIDs embedded in the tuples would be the big problem. 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] Implicit casts to array types
Thanks, that function does help, but it still cannot cast directly to text[]; The point of the functions was only ever to allow for an implicit cast to text[]; My goal is to be able to copy from a simple csv with nonarray entries (1,2,3,...) and extract text arrays when the target table calls for it by creating an array of size 1. I believe this will require a custom implicit cast from unknown to text[], but if there's a better way to do it, I'd love to know. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Implicit-casts-to-array-types-tp5736582p5736588.html Sent from the PostgreSQL - general mailing list archive at Nabble.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
I wrote: Meh. That character renders as \310 in your mail, which is not an assigned code in ISO 8859-1. The numerically corresponding Unicode value would be U+0090, which is an unspecified control character. Oh, scratch that, apparently I can't do hex/octal arithmetic in my head first thing in the morning. It's really U+00C8 which is perfectly valid. I can't see a reason why that character and only that character would be problematic --- have you done systematic testing to confirm that that's the only should-be-LATIN1 character that fails? 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] Implicit casts to array types
joshua jzuel...@arbormetrix.com writes: I'm trying to create an implicit cast from an unknown type into a text array type by creating a simple array of size 1. e.g. create function textarray(unknown) returns text[] as 'select ARRAY[$1::text];' language sql immutable; create cast (unknown as text[]) with function textarray(unknown) as implicit; No, that isn't gonna work. unknown isn't a real type and the parser doesn't use normal casting rules for it. It's just a placeholder until the parser can figure out what type an undecorated literal ought to be. What's your actual problem? (I don't see how this connects to COPY at all --- COPY never does any type inference, nor any implicit casting.) 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
On 12/14/2012 07:30 AM, Tom Lane wrote: Adrian Klaver adrian.kla...@gmail.com writes: On 12/13/2012 06:51 PM, Alvaro Herrera wrote: 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. So it is possible to slide a file under a table? How exactly do you do that and keep the OID and filenode references in sync? The XIDs embedded in the tuples would be the big problem. Well I always figured doing something like this would be equivalent to rebuilding a car engine while it was running:) I am intrigued by the possibility though. Just wondering if it has actually been done successfully. 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] Implicit casts to array types
On Fri, Dec 14, 2012 at 9:32 AM, joshua jzuel...@arbormetrix.com wrote: Thanks, that function does help, but it still cannot cast directly to text[]; The point of the functions was only ever to allow for an implicit cast to text[]; I'm not parsing that. Why do you need an explicit cast? My goal is to be able to copy from a simple csv with nonarray entries (1,2,3,...) and extract text arrays when the target table calls for it by creating an array of size 1. I believe this will require a custom implicit cast from unknown to text[], but if there's a better way to do it, I'd love to know. If I want to handle csv data in an array, I usually do it like this: *) create a one column table for staging the load *) COPY the data to that table, intentionally faking the delimiter *) process via conversion to array with 'string_to_array'. the above only works if there's no commas inside your strings, so a little extra massaging might be required in that case. merlin -- 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
On 12/14/2012 07:35 AM, Tom Lane wrote: I wrote: Meh. That character renders as \310 in your mail, which is not an assigned code in ISO 8859-1. The numerically corresponding Unicode value would be U+0090, which is an unspecified control character. Oh, scratch that, apparently I can't do hex/octal arithmetic in my head first thing in the morning. It's really U+00C8 which is perfectly valid. I can't see a reason why that character and only that character would be problematic --- have you done systematic testing to confirm that that's the only should-be-LATIN1 character that fails? This is where I am confused, in one of the original posts the OP said: JAVA codes work for most of characters, but not -È. 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] Problem starting PG-9.2 on non-default port
James B. Byrne byrn...@harte-lyne.ca writes: On Fri, December 14, 2012 09:52, Adrian Klaver wrote: Assuming an RPM install, see Devrims reply. Might one inquire as to why it is necessary to override the configuration file in the startup script? There's some background about that here: https://bugzilla.redhat.com/show_bug.cgi?id=803295 Recent Fedora RPMs have added the attached patch, which Devrim might care to borrow if he hasn't already. regards, tom lane Add note warning users that Postgres' port number is forced in the service file, mainly because it's traditional in Red Hat installations to set it there rather than in postgresql.conf. (There are minor usability benefits to doing it this way though, for example that the postmaster's port number is visible in ps as part of its command line.) diff -Naur postgresql-9.2rc1.orig/src/backend/utils/misc/postgresql.conf.sample postgresql-9.2rc1/src/backend/utils/misc/postgresql.conf.sample --- postgresql-9.2rc1.orig/src/backend/utils/misc/postgresql.conf.sample 2012-08-23 18:06:49.0 -0400 +++ postgresql-9.2rc1/src/backend/utils/misc/postgresql.conf.sample 2012-09-01 21:57:55.498629897 -0400 @@ -61,6 +61,8 @@ # defaults to 'localhost'; use '*' for all # (change requires restart) #port = 5432# (change requires restart) +# Note: In RHEL/Fedora installations, you can't set the port number here; +# adjust it in the service file instead. #max_connections = 100 # (change requires restart) # Note: Increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). -- 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] Implicit casts to array types
Tom-
My apologies, I'm still somewhat new to this. Specifically, I'm dealing with
COPY FROM CSV. I had assumed that since a csv is essentially a pile of text
and COPY FROM is smart enough to interpret all sorts of csv entries into
postgresql data types that if I wanted to allow a nonstandard conversion,
I'd have to define some sort of cast to allow COPY FROM to interpret, say
...,green,... as {'green}.
Merlin-
I could set this up to use a staging table, but honestly, given our systems,
it'd be easier for me to change all of our source csv's to simply read
...,{abc},... instead of ...,abc,... than to change our code base to use a
series of staging tables (we will be using brackets in the future; this is
more of a backwards compatibility issue). Especially since it currently
doesn't have to inspect the target data type of columns we load up, it
simply allows the COPY FROM command to do all of the interpreting which
brings me back to my original point. :)
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Implicit-casts-to-array-types-tp5736582p5736596.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Implicit casts to array types
On Fri, Dec 14, 2012 at 9:52 AM, joshua jzuel...@arbormetrix.com wrote:
Tom-
My apologies, I'm still somewhat new to this. Specifically, I'm dealing with
COPY FROM CSV. I had assumed that since a csv is essentially a pile of text
and COPY FROM is smart enough to interpret all sorts of csv entries into
postgresql data types that if I wanted to allow a nonstandard conversion,
I'd have to define some sort of cast to allow COPY FROM to interpret, say
...,green,... as {'green}.
Merlin-
I could set this up to use a staging table, but honestly, given our systems,
it'd be easier for me to change all of our source csv's to simply read
...,{abc},... instead of ...,abc,... than to change our code base to use a
series of staging tables (we will be using brackets in the future; this is
more of a backwards compatibility issue). Especially since it currently
doesn't have to inspect the target data type of columns we load up, it
simply allows the COPY FROM command to do all of the interpreting which
brings me back to my original point. :)
If input csv doesn't match your destination structure, then staging
the input to a temporary work table and processing the transformation
with a query is really the way to go. Hacking casts is about as ugly
as it gets.
merlin
--
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] Implicit casts to array types
Merlin Moncure-2 wrote If input csv doesn't match your destination structure, then staging the input to a temporary work table and processing the transformation with a query is really the way to go. Hacking casts is about as ugly as it gets. merlin Thanks. I thought that might be the case. I just wanted to see if there was a way to slightly alter the COPY FROM protocol's functionality. But it'll probably be safest to just update all our csv's. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Implicit-casts-to-array-types-tp5736582p5736602.html Sent from the PostgreSQL - general mailing list archive at Nabble.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
But pg_dirty_read only runs in Linux. It doesnt run in windows.
De: Alvaro Herrera alvhe...@2ndquadrant.com
Para: Alejandro Carrillo faster...@yahoo.es
CC: pgsql-general@postgresql.org pgsql-general@postgresql.org
Enviado: Jueves 13 de diciembre de 2012 21:51
Asunto: Re: [GENERAL] Read recover rows
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 Herrera http://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
Re: [GENERAL] XML Schema for PostgreSQL database
Em 14/12/2012 12:21, Merlin Moncure escreveu: On Thu, Dec 13, 2012 at 5:52 PM, Edson Richter edsonrich...@hotmail.com wrote: Em 13/12/2012 20:10, Merlin Moncure escreveu: On Thu, Dec 13, 2012 at 1:54 PM, Edson Richter edsonrich...@hotmail.com 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). Years ago I decided that the only way to do forward engineering was to capture the changes I make to development databases in scripts and to manually apply those scripts for release management. This process works and like you I've found the various commercial tools to have various weaknesses. So for forward engineering I say: quit using tools and write scripts. Yes, I've developed special tasks to update database automatically based on schema version. But this becomes a hard work very quick (because system grows too fast and we don't have dedicated DBA to deal with all those changes). I'm also like you amazed how poor the various database diagramming tools are -- they all suck. Case Studio used to be pretty good back in the day but I wouldn't recommend it today. My personal take on ERD/diagramming is that: *) diagram generation should be automatic and useful Yes - also, tool must have multi diagram support, in order we can organize different views of the structure. *) human input to adjust the layout should not be required (every time I move the stupid boxes and straighten the stupid lines I feel like I'm finger painting) Yes, I also hate that. DBWrench (tool I use today) has no auto layout of any kind, and I feel like a fool arranging tables when I'm supposed to do something that add value to our customers. *) diagram tool should follow database changes and adjust the diagram accordingly Yes - database diff between database and model design with true change scripts. If I change the name of a column, I do expect Alter table XXX rename column to OOO (or something like that, I can't remember the syntax right now). *) diagram output should be standard html (only) without requiring tool to log in and adjust diagram I don't really care about output - SVG would be excellent, because it scales well and will print nicely. I can tell that a data dictionary with diagramming output would be enough. If I can edit the data dictionary and system keep track of all changes, would be enough for me. If PgAdmin would have the ability to maintain a data dictionary that is not the database itself, and plus the feature to synchronize with database (which would be any PostgreSQL database registered) would be optimal for me. I've come around to the point of view that this is an unfilled niche in the industry. Furthermore, as long as scope is kept reasonably down, this is not a very difficult project. So I've decided (along with Atri) to give it a shot. Iniitial plans is to do plain html dumps directly out of the database and use GraphVis to document dependency flow. That was my thought - if it's not too expensive, I can do something. That's my question regarding XML, would be very easy for me to work with it using Java (my preferred language, which I use on daily basis). XML would work well with versioning systems, and is quite easy to diff etc. If there is anything I can help, let me know. merlin -- 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] Implicit casts to array types
joshua jzuel...@arbormetrix.com writes:
My apologies, I'm still somewhat new to this. Specifically, I'm dealing with
COPY FROM CSV. I had assumed that since a csv is essentially a pile of text
and COPY FROM is smart enough to interpret all sorts of csv entries into
postgresql data types that if I wanted to allow a nonstandard conversion,
I'd have to define some sort of cast to allow COPY FROM to interpret, say
...,green,... as {'green}.
COPY is not smart at all. It just looks at the column types of the
target table and assumes that the incoming data is of those types.
(More precisely, it applies the input conversion function of each
column's data type, after having separated and de-escaped the text
according to datatype-independent format rules.)
I could set this up to use a staging table, but honestly, given our systems,
it'd be easier for me to change all of our source csv's to simply read
...,{abc},... instead of ...,abc,... than to change our code base to use a
series of staging tables
In that case, adjusting the source data is the way to go. Or you could
look at using an external ETL tool to do that for you. We've resisted
putting much transformational smarts into COPY because the main goal
for it is to be as fast and reliable as possible.
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] XML Schema for PostgreSQL database
On Fri, Dec 14, 2012 at 9:47 PM, Edson Richter edsonrich...@hotmail.comwrote: Em 14/12/2012 12:21, Merlin Moncure escreveu: On Thu, Dec 13, 2012 at 5:52 PM, Edson Richter edsonrich...@hotmail.com wrote: Em 13/12/2012 20:10, Merlin Moncure escreveu: On Thu, Dec 13, 2012 at 1:54 PM, Edson Richter edsonrich...@hotmail.com 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). Years ago I decided that the only way to do forward engineering was to capture the changes I make to development databases in scripts and to manually apply those scripts for release management. This process works and like you I've found the various commercial tools to have various weaknesses. So for forward engineering I say: quit using tools and write scripts. Yes, I've developed special tasks to update database automatically based on schema version. But this becomes a hard work very quick (because system grows too fast and we don't have dedicated DBA to deal with all those changes). I'm also like you amazed how poor the various database diagramming tools are -- they all suck. Case Studio used to be pretty good back in the day but I wouldn't recommend it today. My personal take on ERD/diagramming is that: *) diagram generation should be automatic and useful Yes - also, tool must have multi diagram support, in order we can organize different views of the structure. *) human input to adjust the layout should not be required (every time I move the stupid boxes and straighten the stupid lines I feel like I'm finger painting) Yes, I also hate that. DBWrench (tool I use today) has no auto layout of any kind, and I feel like a fool arranging tables when I'm supposed to do something that add value to our customers. *) diagram tool should follow database changes and adjust the diagram accordingly Yes - database diff between database and model design with true change scripts. If I change the name of a column, I do expect Alter table XXX rename column to OOO (or something like that, I can't remember the syntax right now). *) diagram output should be standard html (only) without requiring tool to log in and adjust diagram I don't really care about output - SVG would be excellent, because it scales well and will print nicely. I can tell that a data dictionary with diagramming output would be enough. If I can edit the data dictionary and system keep track of all changes, would be enough for me. If PgAdmin would have the ability to maintain a data dictionary that is not the database itself, and plus the feature to synchronize with database (which would be any PostgreSQL database registered) would be optimal for me. I've come around to the point of view that this is an unfilled niche in the industry. Furthermore, as long as scope is kept reasonably down, this is not a very difficult project. So I've decided (along with Atri) to give it a shot. Iniitial plans is to do plain html dumps directly out of the database and use GraphVis to document dependency flow. That was my thought - if it's not too expensive, I can do something. That's my question regarding XML, would be very easy for me to work with it using Java (my preferred language, which I use on daily basis). XML would work well with versioning systems, and is quite easy to diff etc. If there is anything I can help, let me know. Hi, At the moment, the project is in prototyping phase. We are planning to dump HTML from the database. Once it is ready for testing, we will let you know how you can help(hopefully with testing!) Regards, Atri merlin -- Sent via pgsql-general mailing list
Re: [GENERAL] Implicit casts to array types
Tom Lane-2 wrote COPY is not smart at all. It just looks at the column types of the target table and assumes that the incoming data is of those types. (More precisely, it applies the input conversion function of each column's data type, after having separated and de-escaped the text according to datatype-independent format rules.) In that case, adjusting the source data is the way to go. Or you could look at using an external ETL tool to do that for you. We've resisted putting much transformational smarts into COPY because the main goal for it is to be as fast and reliable as possible. regards, tom lane I see, it's that input conversion function that I would have needed to change. I understand and agree, we depend on COPY FROM in many contexts to upload huge batches of data orders of magnitude faster than INSERT. We've also used it recently as a generic table interface for smaller configuration tables, but you're right: speed and reliability should be the primary focus of COPY FROM. Thanks for all the quick responses, you guys. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Implicit-casts-to-array-types-tp5736582p5736610.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Fwd: [JDBC] Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc)
Hello,
Can anyone please figure out this ?
Begin forwarded message:
From: dmp da...@ttc-cmc.net
Subject: Re: [JDBC] Fwd: [ADMIN] Confuse about the behaveior of
PreparedStatement.executeBatch (jdbc)
Date: December 15, 2012 12:30:50 AM GMT+08:00
To: Haifeng Liu liuhaif...@live.com, pgsql-j...@postgresql.org
Hello,
Perhaps you should pose the question to the server mailing list. It seems that
maybe the sequence is possibly being changed in execution. In a small update,
10, the sequence stays intact, but for a larger update the server perhaps is
optimizing the sequence for execution thereby changing the order. In any case
you appeared to answer your own question.
http://archives.postgresql.org/pgsql-general/
danap.
Haifeng Liu wrote:
example code:
Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(insert/update...);
for (A a : AList) {
pstmt.setParameter(...);
pstmt.addBatch();
}
pstmt.executeBatch();
I did a simple test and found that if one of the batch failed, the other
update may be execute partially. when I test with 10 updates a batch, none
of them are updated, when I test with 1000 updates a batch, about 700+ of
them are executed, but the failed update should be the last one, which means
999 executed updates is more reasonable than 700+. But really weird thing is
the getUpdateCounts method returns the reasonable information.
I don't know what happened inside this driver. Currently I have to
setAutoCommit to false and use commit/rollback to achieve my goal.
On Dec 14, 2012, at 11:56 PM, dmpda...@ttc-cmc.net wrote:
Hello,
Though a simple example of your code would provide a better response
from the mailing list, I will speculate based on the context of the
Java 6 API, statement class and your comments. Yes, the batch appears
to be proceeding.
Re: [GENERAL] XML Schema for PostgreSQL database
On Fri, Dec 14, 2012 at 10:17 AM, Edson Richter edsonrich...@hotmail.com wrote: Em 14/12/2012 12:21, Merlin Moncure escreveu: On Thu, Dec 13, 2012 at 5:52 PM, Edson Richter edsonrich...@hotmail.com wrote: Em 13/12/2012 20:10, Merlin Moncure escreveu: On Thu, Dec 13, 2012 at 1:54 PM, Edson Richter edsonrich...@hotmail.com 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). Years ago I decided that the only way to do forward engineering was to capture the changes I make to development databases in scripts and to manually apply those scripts for release management. This process works and like you I've found the various commercial tools to have various weaknesses. So for forward engineering I say: quit using tools and write scripts. Yes, I've developed special tasks to update database automatically based on schema version. But this becomes a hard work very quick (because system grows too fast and we don't have dedicated DBA to deal with all those changes). I'm also like you amazed how poor the various database diagramming tools are -- they all suck. Case Studio used to be pretty good back in the day but I wouldn't recommend it today. My personal take on ERD/diagramming is that: *) diagram generation should be automatic and useful hrm, I just found schemaspy. It looks pretty nice. merlin -- 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] Fwd: [JDBC] Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc)
Haifeng Liu wrote: Can anyone please figure out this ? This was just answered on the JDBC list by Kris Jurka: Internally the driver splits each batch into sub-batches of 250 to send to the server to reduce the possibility of deadlocking the network connection. When auto-commit is enabled in the driver, the auto-commit setting affects the whole sub-batch of 250 because of how the frontend/backend protocol works. So you are seeing some oddities because of some implementation details and you should not use auto-commit with batch statements. -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] Problem starting PG-9.2 on non-default port
On Fri, December 14, 2012 10:21, James B. Byrne wrote: On Fri, December 14, 2012 09:52, Adrian Klaver wrote: Assuming an RPM install, see Devrims reply. Might one inquire as to why it is necessary to override the configuration file in the startup script? This is a patch for /etc/init.d/postgresql-9.2 to retrieve the port from the postgresql.conf file and override the default setting if it is set there. The last two calls to 'cut' handle both tabs and spaces as whitespace. 76a77,88 # Override port setting from postgresql.conf if set PGCONFPORT=$(grep -e ^port $PGDATA/postgresql.conf | \ cut -d = -f 2 | \ sed -e 's/^[ \t]*//' | \ cut -f1 | \ cut -d -f 1) if [[ -n $PGCONFPORT ]] # -n == defined and not blank then echo Setting PGPORT to $PGCONFPORT from $PGDATA/postgresql.conf PGPORT=$PGCONFPORT fi -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Problem starting PG-9.2 on non-default port
On Fri, December 14, 2012 10:21, James B. Byrne wrote: This is a patch for /etc/init.d/postgresql-9.2 to retrieve the port from the postgresql.conf file and override the default setting if it A slight, but important, correction that handles leading white space in the first selection: 76a77,88 # Override port setting from postgresql.conf if set PGCONFPORT=$(grep -e ^\s*port $PGDATA/postgresql.conf | \ cut -d = -f 2 | \ sed -e 's/^[ \t]*//' | \ cut -f1 | \ cut -d -f 1) if [[ -n $PGCONFPORT ]] # -n == defined and non-blank -z == not defined then echo Setting PGPORT to $PGCONFPORT from $PGDATA/postgresql.conf PGPORT=$PGCONFPORT fi -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres PANIC : heap_insert_redo: failed to add tuple
Gents, after restart service is not coming up, seems like something is corrupted. can i get recover data somehow?[?] 2012-12-14 17:49:28 EET LOG: could not create IPv6 socket: Address family not supported by protocol 2012-12-14 17:49:28 EET LOG: database system was interrupted while in recovery at 2012-12-14 17:10:01 EET 2012-12-14 17:49:28 EET HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. 2012-12-14 17:49:28 EET LOG: database system was not properly shut down; automatic recovery in progress 2012-12-14 17:49:28 EET LOG: incomplete startup packet 2012-12-14 17:49:28 EET LOG: redo starts at 8/EB84892C 2012-12-14 17:49:29 EET FATAL: the database system is starting up 2012-12-14 17:49:29 EET FATAL: the database system is starting up 2012-12-14 17:49:30 EET FATAL: the database system is starting up 2012-12-14 17:49:30 EET FATAL: the database system is starting up 2012-12-14 17:49:30 EET WARNING: will not overwrite a used ItemId 2012-12-14 17:49:30 EET CONTEXT: xlog redo insert: rel 1663/1629517/12496; tid 23/20 2012-12-14 17:49:30 EET PANIC: heap_insert_redo: failed to add tuple 2012-12-14 17:49:30 EET CONTEXT: xlog redo insert: rel 1663/1629517/12496; tid 23/20 2012-12-14 17:49:30 EET LOG: startup process (PID 25814) was terminated by signal 6: Aborted 2012-12-14 17:49:30 EET LOG: aborting startup due to startup process failure 361.gif
Re: [GENERAL] initdb error
David Noel david.i.n...@gmail.com writes: I didn't have any luck with the rc script but I was able to use it to get a ktrace dump as root (ktrace as user pgsql doesn't seem to work). So hopefully that will show something(!) The relevant part of the ktrace output is 71502 postgres CALL unlink(0x7fffc130) 71502 postgres NAMI pg_xlog/xlogtemp.71502 71502 postgres RET unlink -1 errno 2 No such file or directory 71502 postgres CALL open(0x7fffc130,O_RDWR|O_CREAT|O_EXCL,S_IRUSR|S_IWUSR) 71502 postgres NAMI pg_xlog/xlogtemp.71502 71502 postgres RET open 3 71502 postgres CALL write(0x3,0x801a56030,0x2000) 71502 postgres GIO fd 3 wrote 4096 bytes a lot of uninteresting write() calls snipped ... 71502 postgres RET write 8192/0x2000 71502 postgres CALL close(0x3) 71502 postgres RET close 0 71502 postgres CALL unlink(0x7fffbc60) 71502 postgres NAMI pg_xlog/00010001 71502 postgres RET unlink -1 errno 2 No such file or directory 71502 postgres CALL link(0x7fffc130,0x7fffbc60) 71502 postgres NAMI pg_xlog/xlogtemp.71502 71502 postgres NAMI pg_xlog/00010001 71502 postgres RET link -1 errno 1 Operation not permitted 71502 postgres CALL unlink(0x7fffc130) 71502 postgres NAMI pg_xlog/xlogtemp.71502 71502 postgres RET unlink 0 71502 postgres CALL open(0x7fffc530,O_RDWR,unused0x180) 71502 postgres NAMI pg_xlog/00010001 71502 postgres RET open -1 errno 2 No such file or directory This corresponds to the execution of XLogFileInit(), and what's evidently happening is that we successfully create and zero-fill the first xlog segment file under a temporary name, but then the attempt to rename it into place with link() fails with EPERM. This is really a WTF kind of failure, I think. The directory is certainly writable --- it was made under our own UID, and what's more we just managed to create the file there under its temp name. So how can we get an EPERM failure from link()? I think this is a kernel bug. regards, tom lane PS: one odd thing here is that the ereport(LOG) in InstallXLogFileSegment isn't doing anything; otherwise we'd have gotten a much more helpful error report about could not link file. I don't think we run the bootstrap mode with log_min_messages set high enough to disable LOG messages, so why isn't it printing? Nonetheless, this error shouldn't have occurred. -- 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
Hello All,
Meh. That character renders as \310 in your mail, which is not an
assigned code in ISO 8859-1. The numerically corresponding Unicode
value would be U+0090, which is an unspecified control character.
Oh, scratch that, apparently I can't do hex/octal arithmetic in my
head first thing in the morning. It's really U+00C8 which is perfectly
valid. I can't see a reason why that character and only that character
would be problematic --- have you done systematic testing to confirm
that that's the only should-be-LATIN1 character that fails?
Finally, the problem is resolved:
SHOW VARIABLES LIKE character\_set\_%;
+--++
| Variable_name| Value |
+--++
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results| latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 | -- here mysql uses utf8 for
character_set_system.
Change my java code to:
public static String utf8_to_mysql(String str)
throws Exception
{
try
{
byte[] convertStringToByte = str.getBytes(UTF-8);
str= new String(convertStringToByte,
UTF-8);
return str;
}catch(Exception e)
{
log.error(utf8_to_latin1 Error: + e.getMessage());
log.error(e);
throw e;
}
Have to explicitly specify UTF-8, but cannot leave as empty.
Larry's comments(from MyBatis mailing list) and I tried both from/to
by UTF8. It works. This is still little bit strange to me. But it works!
My guess is that it's correct but the client you're using is messing
it up. If not, then you need to look at your connection strings to
the 2 databases to make sure they are handling the encodings
correctly.Unless you set them specifically, I suspect they are using
your default system encoding - so both may be using utf8 or iso8859.
Thank you very much for all of your help for this!
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] Read recover rows
Alejandro Carrillo escribió: But pg_dirty_read only runs in Linux. It doesnt run in windows. So port it. There's no fundamental reason for it not to work. -- Á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
Re: [GENERAL] initdb error
On 12/14/12, Tom Lane t...@sss.pgh.pa.us wrote: David Noel david.i.n...@gmail.com writes: I didn't have any luck with the rc script but I was able to use it to get a ktrace dump as root (ktrace as user pgsql doesn't seem to work). So hopefully that will show something(!) The relevant part of the ktrace output is 71502 postgres CALL unlink(0x7fffc130) 71502 postgres NAMI pg_xlog/xlogtemp.71502 71502 postgres RET unlink -1 errno 2 No such file or directory 71502 postgres CALL open(0x7fffc130,O_RDWR|O_CREAT|O_EXCL,S_IRUSR|S_IWUSR) 71502 postgres NAMI pg_xlog/xlogtemp.71502 71502 postgres RET open 3 71502 postgres CALL write(0x3,0x801a56030,0x2000) 71502 postgres GIO fd 3 wrote 4096 bytes a lot of uninteresting write() calls snipped ... 71502 postgres RET write 8192/0x2000 71502 postgres CALL close(0x3) 71502 postgres RET close 0 71502 postgres CALL unlink(0x7fffbc60) 71502 postgres NAMI pg_xlog/00010001 71502 postgres RET unlink -1 errno 2 No such file or directory 71502 postgres CALL link(0x7fffc130,0x7fffbc60) 71502 postgres NAMI pg_xlog/xlogtemp.71502 71502 postgres NAMI pg_xlog/00010001 71502 postgres RET link -1 errno 1 Operation not permitted 71502 postgres CALL unlink(0x7fffc130) 71502 postgres NAMI pg_xlog/xlogtemp.71502 71502 postgres RET unlink 0 71502 postgres CALL open(0x7fffc530,O_RDWR,unused0x180) 71502 postgres NAMI pg_xlog/00010001 71502 postgres RET open -1 errno 2 No such file or directory This corresponds to the execution of XLogFileInit(), and what's evidently happening is that we successfully create and zero-fill the first xlog segment file under a temporary name, but then the attempt to rename it into place with link() fails with EPERM. This is really a WTF kind of failure, I think. The directory is certainly writable --- it was made under our own UID, and what's more we just managed to create the file there under its temp name. So how can we get an EPERM failure from link()? I think this is a kernel bug. regards, tom lane PS: one odd thing here is that the ereport(LOG) in InstallXLogFileSegment isn't doing anything; otherwise we'd have gotten a much more helpful error report about could not link file. I don't think we run the bootstrap mode with log_min_messages set high enough to disable LOG messages, so why isn't it printing? Nonetheless, this error shouldn't have occurred. Thanks so much for the analysis. Where to from here? The freebsd-datab...@freebsd.org mailing list? The postgresql port maintainer? Who should I be in touch with? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PgSQL 9.1: Warning - error 10061 on Windows, no error on Linux - but connection is broken
I just discovered a non PostgreSQL problem (but I was suspecting all the time from PostgreSQL). I'm recording this because would save lot of time from others in the list, since my problem is already solved. During this day, we had very busy servers and suddenly we started to get error 500 and 502 on our Java server, after a select, update or insert. - Looking web server logs shows no error. - Looking PostgreSQL logs, in Windows server I saw error winsock error 10061, but in Linux server I've found no evidence of the problem. After digging for an hour, I've discovered our connection pool (max 100 connections, 50 idle) have been configured (probably by me) to drop connections if they don't return in 2 milliseconds (maxWait=2)... HUGE mistake. Changed connection pool parameter to 60 seconds (maxWait=6), and problem has gone. Just my 2c, Edson Richter -- 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
I modified the shared_buffer=50 MB and maintenance_work_mem = 50 MB But still getting the same error. On Thu, Dec 13, 2012 at 7:36 PM, Kevin Grittner kgri...@mail.com wrote: 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 3857 -/+ buffers/cache: 114 909 Swap: 10270 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
Re: [GENERAL] JDBC to load UTF8@psql to latin1@mysql
On 12/14/2012 01:37 PM, Emi Lu wrote: Hello All, Meh. That character renders as \310 in your mail, which is not an assigned code in ISO 8859-1. The numerically corresponding Unicode value would be U+0090, which is an unspecified control character. Oh, scratch that, apparently I can't do hex/octal arithmetic in my head first thing in the morning. It's really U+00C8 which is perfectly valid. I can't see a reason why that character and only that character would be problematic --- have you done systematic testing to confirm that that's the only should-be-LATIN1 character that fails? Finally, the problem is resolved: SHOW VARIABLES LIKE character\_set\_%; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | -- here mysql uses utf8 for character_set_system. Another try is that if I change my client tool encoding set, I do not even need my java transition. All right, good to learn from this. Emi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Quickly making a column non-nullable (without a table scan)
Hi, I have an index on a column that can be nullable. I decide the column shouldn't be nullable anymore. So I alter the column to be not nullable. That alter column query does a full table scan, which can be painful for large tables. Couldn't that index be used instead? Thanks, Joe -- 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 david.i.n...@gmail.com writes: On 12/14/12, Tom Lane t...@sss.pgh.pa.us wrote: This corresponds to the execution of XLogFileInit(), and what's evidently happening is that we successfully create and zero-fill the first xlog segment file under a temporary name, but then the attempt to rename it into place with link() fails with EPERM. This is really a WTF kind of failure, I think. The directory is certainly writable --- it was made under our own UID, and what's more we just managed to create the file there under its temp name. So how can we get an EPERM failure from link()? I think this is a kernel bug. Thanks so much for the analysis. Where to from here? The freebsd-datab...@freebsd.org mailing list? The postgresql port maintainer? Who should I be in touch with? You need to talk to some FreeBSD kernel hackers about why link() might be failing here. Since you see it on UFS too, we can probably exonerate the ZFS filesystem-specific code. I did some googling and found that EPERM can be issued if the filesystem doesn't support hard links (which shouldn't apply to ZFS I trust). Also, Linux has a protected_hardlinks option that causes certain attempts at creating hard links to fail --- but our use-case here doesn't fall foul of any of those restrictions AFAICS, and of course FreeBSD isn't Linux. Still, I wonder if you're running into some misdesigned or misimplemented security restriction. You might want to look at your kernel parameters and see if any of them look like they might have to do with restricting hard-link operations. Also, since Amitabh failed to duplicate the failure on both earlier and later FreeBSD kernels, and we've not heard reports of this from anybody else either, it seems more than possible that it's a plain old bug in the specific kernel version you're using. As a short-term workaround, I'd suggest rebuilding with HAVE_WORKING_LINK disabled. (Just remove that #define from src/include/pg_config_manual.h and rebuild.) 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] PgSQL 9.1: Warning - error 10061 on Windows, no error on Linux - but connection is broken
You do not log failed connection attempts from your Java application? Your desire is commendable but is your only advice: don't set connection timeout to 2ms? What could these products (not you, by setting up better logging) do to minimize the amount of time you had to spend diagnosing the problem? If they already can be configured to do so, and were not in your case, what configuration option values would have helped you to diagnose more quickly (so other do not disable/change those settings and/or why you thought to change them in the first place)? David J. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Edson Richter Sent: Friday, December 14, 2012 2:58 PM To: pgsql-general Subject: [GENERAL] PgSQL 9.1: Warning - error 10061 on Windows, no error on Linux - but connection is broken I just discovered a non PostgreSQL problem (but I was suspecting all the time from PostgreSQL). I'm recording this because would save lot of time from others in the list, since my problem is already solved. During this day, we had very busy servers and suddenly we started to get error 500 and 502 on our Java server, after a select, update or insert. - Looking web server logs shows no error. - Looking PostgreSQL logs, in Windows server I saw error winsock error 10061, but in Linux server I've found no evidence of the problem. After digging for an hour, I've discovered our connection pool (max 100 connections, 50 idle) have been configured (probably by me) to drop connections if they don't return in 2 milliseconds (maxWait=2)... HUGE mistake. Changed connection pool parameter to 60 seconds (maxWait=6), and problem has gone. Just my 2c, Edson Richter -- 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
Re: [GENERAL] pg_restore error with out of memory
AI Rumman wrote: I modified the shared_buffer=50 MB and maintenance_work_mem = 50 MB But still getting the same error. I hope you restarted the PostgreSQL server? If so, pg_restore might just need more RAM than that machine has to deal with that particular database. You might try a text dump, loaded by psql instead. -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] PgSQL 9.1: Warning - error 10061 on Windows, no error on Linux - but connection is broken
Em 14/12/2012 18:14, David Johnston escreveu: You do not log failed connection attempts from your Java application? Can you imagine 100 users attempting (and failing) to get connection every 2 milliseconds would just drop all the server :-) Your desire is commendable but is your only advice: don't set connection timeout to 2ms? Actually, I've spend an hour checking for error 10061 in the Internet and in this mail list archives. Everyone was pointing to dll problems in windows, reinstalling operating system etc. My short recommendation is to check if the connection is not being dropped by the connection pool just too soon. May save someone else an hour of troubleshooting... What could these products (not you, by setting up better logging) do to minimize the amount of time you had to spend diagnosing the problem? If they already can be configured to do so, and were not in your case, what configuration option values would have helped you to diagnose more quickly (so other do not disable/change those settings and/or why you thought to change them in the first place)? This is an interesting question. 1st, I don't know if there is better loggin to set (I can't afford higher log level in production servers). To improve, IMHO, the jdbc pool tooling would standardize parameters, because some are defined in seconds, others in milliseconds. When I did set the maxWait=2, I thought it was 2 seconds. After re-reading documentation, I realized it was in milliseconds. In the referred documentation, another parameter next is set in seconds. So, this is the cause of the confusion. Anyway, I hope this advice save someone else time. Regards, Edson David J. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Edson Richter Sent: Friday, December 14, 2012 2:58 PM To: pgsql-general Subject: [GENERAL] PgSQL 9.1: Warning - error 10061 on Windows, no error on Linux - but connection is broken I just discovered a non PostgreSQL problem (but I was suspecting all the time from PostgreSQL). I'm recording this because would save lot of time from others in the list, since my problem is already solved. During this day, we had very busy servers and suddenly we started to get error 500 and 502 on our Java server, after a select, update or insert. - Looking web server logs shows no error. - Looking PostgreSQL logs, in Windows server I saw error winsock error 10061, but in Linux server I've found no evidence of the problem. After digging for an hour, I've discovered our connection pool (max 100 connections, 50 idle) have been configured (probably by me) to drop connections if they don't return in 2 milliseconds (maxWait=2)... HUGE mistake. Changed connection pool parameter to 60 seconds (maxWait=6), and problem has gone. Just my 2c, Edson Richter -- 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
Re: [GENERAL] Quickly making a column non-nullable (without a table scan)
Joe Van Dyk wrote: I have an index on a column that can be nullable. I decide the column shouldn't be nullable anymore. So I alter the column to be not nullable. That alter column query does a full table scan, which can be painful for large tables. Couldn't that index be used instead? Interesting idea, but I'm pretty sure we don't yet have such logic. -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] Problems with a custom LOCALE
Having got the 9.2 instance running in parallel with the previous
version I am now encountering this difficulty:
Couldn't create database for {adapter=postgresql,
collate=en_US.UTF-8, ctype=en_US.UTF-8, encoding=UTF8,
database=hll_th_forex_development,
host=inet01.hamilton.harte-lyne.ca, pool=5,
password=hll_theheart_devl_password, sslmode=require,
template=template1, username=hll_theheart_db_devl}
PG::Error: ERROR: encoding UTF8 does not match locale
en...@-mmm-dd.utf-8
DETAIL: The chosen LC_CTYPE setting requires encoding LATIN1.
: CREATE DATABASE hll_th_forex_test ENCODING = 'UTF8' TEMPLATE =
template1
I am constructing this remotely using a Rake task borrowed from Ruby
on Rails 3.2.9 and employing ActiveRecord-3.2.9. We use a custom
local on all our servers en...@-mmm-dd.utf-8 which differs from
en_CA only in the presentation of the date. However, regardless of
the system setting, postgresql.conf was configured thus:
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8' # locale for system error message
# strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
So, what is going wrong here?
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrnemailto:byrn...@harte-lyne.ca
Harte Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
--
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] Problems with a custom LOCALE
James B. Byrne byrn...@harte-lyne.ca writes: PG::Error: ERROR: encoding UTF8 does not match locale en...@-mmm-dd.utf-8 DETAIL: The chosen LC_CTYPE setting requires encoding LATIN1. : CREATE DATABASE hll_th_forex_test ENCODING = 'UTF8' TEMPLATE = template1 I am constructing this remotely using a Rake task borrowed from Ruby on Rails 3.2.9 and employing ActiveRecord-3.2.9. We use a custom local on all our servers en...@-mmm-dd.utf-8 which differs from en_CA only in the presentation of the date. Sorta looks like you based that locale on an ISO 8859-1 locale, not a UTF8 locale. AFAICT from looking at the code, PG is seeing that nl_langinfo(CODESET) returns ISO-8859-1 or some variant spelling, so it complains. 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
You need to talk to some FreeBSD kernel hackers about why link() might be failing here. Since you see it on UFS too, we can probably exonerate the ZFS filesystem-specific code. I did some googling and found that EPERM can be issued if the filesystem doesn't support hard links (which shouldn't apply to ZFS I trust). Also, Linux has a protected_hardlinks option that causes certain attempts at creating hard links to fail --- but our use-case here doesn't fall foul of any of those restrictions AFAICS, and of course FreeBSD isn't Linux. Still, I wonder if you're running into some misdesigned or misimplemented security restriction. You might want to look at your kernel parameters and see if any of them look like they might have to do with restricting hard-link operations. Also, since Amitabh failed to duplicate the failure on both earlier and later FreeBSD kernels, and we've not heard reports of this from anybody else either, it seems more than possible that it's a plain old bug in the specific kernel version you're using. As a short-term workaround, I'd suggest rebuilding with HAVE_WORKING_LINK disabled. (Just remove that #define from src/include/pg_config_manual.h and rebuild.) OK, thanks. I've gotten in touch with the freebsd-hackers mailing list. Hopefully we'll be able to get this one figured out. I compiled a GENERIC kernel and tried it again. Still no luck. But at least we know now it wasn't a configuration error. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
