Re: [GENERAL] Need to run a job in PgAdmin-III
On Mon, Sep 24, 2012 at 12:27 PM, John R Pierce wrote: > On 09/23/12 11:24 PM, pavithra wrote: > >> I have pgAdmin-III. It has version 1.12.3. I would like to run a job. I >> have schedule an SQL Job. But when i run there is no output. Can anybody >> let me know what needs to be done?. Also, Can any one tell, where i need to >> look for the errors, when i run a job? >> > > Hope below link help: http://www.pgadmin.org/docs/dev/pgagent-jobs.html --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
[GENERAL] Memory issues
Hi,
I'm using and Amazon ec2 instance with the following spec and the
application that I'm running uses a postgres DB 9.1.
The app has 3 main cron jobs.
*Ubuntu 12, High-Memory Extra Large Instance
17.1 GB of memory
6.5 EC2 Compute Units (2 virtual cores with 3.25 EC2 Compute Units each)
420 GB of instance storage
64-bit platform*
I've changed the main default values under file *postgresql.conf* to:
shared_buffers = 4GB
work_mem = 16MB
wal_buffers = 16MB
checkpoint_segments = 32
effective_cache_size = 8GB
When I run the app, after an hour or two, free -m looks like below ans the
crons can't run due to memory loss or similar (i'm new to postgres and db
admin).
Thanks!
free -m, errors:
total used free shared buffers cached
Mem: 17079 13742 3337 0 64 11882
-/+ buffers/cache: 1796 15283
Swap: 511 0 511
total used *free* shared buffers cached
Mem: 17079 16833 *245 *0 42 14583
-/+ buffers/cache: 2207 14871
Swap: 511 0 511
**free above stays low even when nothing is running.
**errors:
*DBI connect('database=---;host=localhost','postgres',...) failed: could
not fork new process for connection: Cannot allocate memory*
could not fork new process for connection: Cannot allocate memory
and
execute failed: ERROR: out of memory
DETAIL: Failed on request of size 968. [for Statement "
SELECT DISTINCT
Thank you!
[GENERAL] enter/leave session triggers
Hi, Have there been any discussion on the list on triggers fires when new session (libpg session to server?) is opened/closed? Somehow I cannot google anything with the keywords I use. And the question is: has postgresql any plans to have: 1. triggers that fire BEFORE/AFTER user opens a session with server? 2. triggers that fire BEFORE/AFTER user ENTERS/LEAVES a role? the simplist use is to have session timestamps logged in application speciffic tables; but I personaly find myself always having a "users" table, full of attributs which quite often are there to setup user profiles for the duration of sessions and to be persistent across sessions. -R -- 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] Need to run a job in PgAdmin-III
I have a scheduled a sql query as "select sysdate from dual" and i have given as "Data Export". Moreover i cant see the debugger option, or any error log where i can see it, Can you tell me any suggestions on this?. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Need-to-run-a-job-in-PgAdmin-III-tp5725093p5725107.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] Need to run a job in PgAdmin-III
Mandag 24. september 2012 11.06.32 skrev pavithra : > I have a scheduled a sql query as "select sysdate from dual" and i have > given as "Data Export". That's an Oraclism. Have you actually tested the query in psql? postgres=> select sysdate from dual; ERROR: relation "dual" does not exist LINE 1: select sysdate from dual; ^ postgres=> Maybe this is what you want? postgres=> select current_date; date 2012-09-24 (1 row) postgres=> regards, Leif -- 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] Need to run a job in PgAdmin-III
I dont get my psql console itself disabled in the plug-ins menu.Is there a way i can see the error log?. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Need-to-run-a-job-in-PgAdmin-III-tp5725093p5725110.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] Question about upgrading extensions
Chris Travers wrote: > If one releases an extension with say a version number of 0.1 and then releases one with important > changes at 0.2, how is the best way to manage these changes? I couldn't find anything in the docs to > discuss this issue. Am I missing something? > > Specifically for pg_message_queue, for 0.2 I would like to change the type of a field from text > (constrained to 'text', 'xml' or 'bytea) to regtype. Not sure where to put the DDL for an upgrade. Into pg_message_queue--0.1--0.2.sql. See http://www.postgresql.org/docs/current/static/extend-extensions.html#AEN 54163 The command to upgrade an extension is ALTER EXTENSION UPDATE. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Upgrade from 8.4.13 to 9.2.0.1 successful but it still displays 8.4.13 if SELECT version();
On 2012-09-23, a...@hsk.hk wrote: > Hi, > > I have upgraded postgresql 8.4.13 to 9.2.0.1 O/S Ubuntu, restarted > postgresql, > it displayed my postgresql is 9.2 but when I log into postgresql, show > version, > it is still 8.4.13, see a) and b) below, read the man pages for pg_upgradecluster and postgresql-common -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Python mapping with new 9.2 data types
Hello, I've worked at Python-Postgres mapping for the new PostgreSQL 9.2 data types. They should be released with the next psycopg2 version. The current design, susceptible to changes if needed, is documented here: - JSON adaptation: http://initd.org/psycopg/docs/extras.html#json-adaptation - Range data types: http://initd.org/psycopg/docs/extras.html#range-data-types The code is available in feature branches of my github repository: https://github.com/dvarrazzo/psycopg Any feedback (tests, comments, docs corrections) is very welcome. Regards, -- Daniele -- 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] 9.1 vs 8.4 performance
Hello Guys, Thanks for reply, and sorry for late response. Here is more details. 1. Both servers are installed on parallel on the same machine, so there is no difference in Hardware. 2. Both servers have the same configuration setting except the ports (5432 and 5433). 3. I have run VACUUM ANALYSE command on both databases and no change in performance for both of them. 4. The execution plans are certainly different, I tried to use diff and the graphical representation pg-admin but the execution plan is a around 1700 lines, this is why I did not post them. I have noticed that, the filter location varies whe run the same queries using the two servers. In 8.4 the execution plan is mostly filter then join. In 9.1 join and then filter. But this is just a rough overview. Regards From: "Gauthier, Dave" To: Scott Marlowe ; salah jubeh Cc: pgsql Sent: Friday, September 21, 2012 9:40 PM Subject: Re: [GENERAL] 9.1 vs 8.4 performance One thing I sometimes forget to do after loading up an empty DB with data is to run "analyze". I usually "remember" once I see poor query performance, run the analyze, and its fixed. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Scott Marlowe Sent: Friday, September 21, 2012 3:01 PM To: salah jubeh Cc: pgsql Subject: Re: [GENERAL] 9.1 vs 8.4 performance On Fri, Sep 21, 2012 at 8:32 AM, salah jubeh wrote: > Hello, > > I have two postgresql servers 9.1.5 and 8.4.8 running on ubuntu > machine, both are fresh installs and both has the same configuration > files and databases. > > I am running queries sequentially on each machine using a database > dumped from a life server , and 9.1 server is much slower than 8.4. So how different or similar are these two machines? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Timeline switch problem with streaming replication with 3 nodes
Hi All I've set up a 3 postgresql nodes 1 master and 2 slaves. They have been configured for streaming replication with synchronous on. I've set up an virtual IP that points to the current master node. When I kill the master node. The slave that was synchronous gets promoted to master and gets the shared virtual IP But sometimes the other slave don't accept the switch and instead the log on the slave says: 2012-09-24 10:45:06 GMT 4663 FATAL: replication terminated by primary server 2012-09-24 10:45:06 GMT 4662 LOG: record with zero length at 0/29E8 2012-09-24 10:45:06 GMT 10209 FATAL: could not connect to the primary server: could not connect to server: Connection refused Is the server running on host "10.216.73.60" and accepting TCP/IP connections on port 5432? 2012-09-24 10:45:11 GMT 10272 FATAL: could not connect to the primary server: FATAL: recovery is still in progress, can't accept WAL streaming connections 2012-09-24 10:45:16 GMT 10326 FATAL: timeline 10 of the primary does not match recovery target timeline 9 2012-09-24 10:45:21 GMT 10388 FATAL: timeline 10 of the primary does not match recovery target timeline 9 2012-09-24 10:45:26 GMT 10451 FATAL: timeline 10 of the primary does not match recovery target timeline 9 ... And it continues to repeat the last line. The new master says: 2012-09-24 10:45:06 GMT 8394 FATAL: replication terminated by primary server 2012-09-24 10:45:06 GMT 8393 LOG: record with zero length at 0/29E8 2012-09-24 10:45:11 GMT 8393 LOG: trigger file found: /tmp/postgresql_trigger 2012-09-24 10:45:11 GMT 8393 LOG: redo done at 0/2990 2012-09-24 10:45:11 GMT 8393 LOG: last completed transaction was at log time 2012-09-24 10:45:01.917175+00 2012-09-24 10:45:11 GMT 8393 LOG: selected new timeline ID: 10 2012-09-24 10:45:11 GMT 10741 [unknown] FATAL: recovery is still in progress, can't accept WAL streaming connections 2012-09-24 10:45:12 GMT 8393 LOG: archive recovery complete 2012-09-24 10:45:12 GMT 8391 LOG: database system is ready to accept connections 2012-09-24 10:45:12 GMT 10743 LOG: autovacuum launcher started The recovery.conf is: standby_mode = 'on' primary_conninfo = 'host=10.216.73.60 port=5432 user=root password=onyx application_name=10.216.73.195' recovery_target_timeline = 'latest' trigger_file = '/tmp/postgresql_trigger' I've found a discussion (http://archives.postgresql.org/pgsql-general/2011-12/msg00553.php) on a similar issue a while back. They talk about sharing WAL files as the solution. But I thought that the idea with streaming replication was that I would not need a shared storage. Is that the only solution or is there another way? Best regards, Mads -- 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] 9.1 vs 8.4 performance
On Mon, Sep 24, 2012 at 5:47 AM, salah jubeh wrote: > Hello Guys, > > Thanks for reply, and sorry for late response. Here is more details. > > 1. Both servers are installed on parallel on the same machine, so there is > no difference in Hardware. > 2. Both servers have the same configuration setting except the ports (5432 > and 5433). > 3. I have run VACUUM ANALYSE command on both databases and no change in > performance for both of them. > 4. The execution plans are certainly different, I tried to use diff and the > graphical representation pg-admin but the execution plan is a around 1700 > lines, this is why I did not post them. > > I have noticed that, the filter location varies whe run the same queries > using the two servers. In 8.4 the execution plan is mostly filter then join. > In 9.1 join and then filter. But this is just a rough overview. > > Regards > > > > > From: "Gauthier, Dave" > To: Scott Marlowe ; salah jubeh > Cc: pgsql > Sent: Friday, September 21, 2012 9:40 PM > > Subject: Re: [GENERAL] 9.1 vs 8.4 performance > > One thing I sometimes forget to do after loading up an empty DB with data is > to run "analyze". I usually "remember" once I see poor query performance, > run the analyze, and its fixed. > > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Scott Marlowe > Sent: Friday, September 21, 2012 3:01 PM > To: salah jubeh > Cc: pgsql > Subject: Re: [GENERAL] 9.1 vs 8.4 performance > > On Fri, Sep 21, 2012 at 8:32 AM, salah jubeh wrote: >> Hello, >> >> I have two postgresql servers 9.1.5 and 8.4.8 running on ubuntu >> machine, both are fresh installs and both has the same configuration >> files and databases. >> >> I am running queries sequentially on each machine using a database >> dumped from a life server , and 9.1 server is much slower than 8.4. > > So how different or similar are these two machines? We need to see the plans. 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] Running CREATE only on certain Postgres versions
I have some code which creates a function in Postgres, taken from
http://wiki.postgresql.org/wiki/Array_agg .
DROP AGGREGATE IF EXISTS array_agg(anyelement);
CREATE AGGREGATE array_agg(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
INITCOND='{}'
);
The function was added in 8.4, and so the code fails when run on 8.4 or higher.
How can I make the code cross-version compatible? For instance, how
can I tell it to check the version, and only run if 8.3 or lower? Or
another way to make it cross-version?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Prolem to acess PostgeSQL from other mechine
Hi
I have some problem to connect PostgeSQL server from client mechine, error and
code is given below. I am using Windows 7 OS
Code
public static void main(String[] args) {
try {
System.out.println("Connecting..");
Class.forName("org.postgresql.Driver").newInstance();
connection =
DriverManager.getConnection("jdbc:postgresql://10.10.10.101:6060/mydb",
"postgres", "welcome");
} catch (Exception e) {
e.printStackTrace();
}
}
Error
org.postgresql.util.PSQLException: Connection refused. Check that the hostname
and port are correct and
that the postmaster is accepting TCP/IP connections.
at
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:136)
at
org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
at
org.postgresql.jdbc2.AbstractJdbc2Connection.(AbstractJdbc2Connection.java:123)
at
org.postgresql.jdbc3.AbstractJdbc3Connection.(AbstractJdbc3Connection.java:28)
at
org.postgresql.jdbc3g.AbstractJdbc3gConnection.(AbstractJdbc3gConnection.java:20)
at
org.postgresql.jdbc4.AbstractJdbc4Connection.(AbstractJdbc4Connection.java:30)
at org.postgresql.jdbc4.Jdbc4Connection.(Jdbc4Connection.java:22)
at org.postgresql.Driver.makeConnection(Driver.java:391)
at org.postgresql.Driver.connect(Driver.java:265)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at com.web.view.TestLogic.main(TestLogic.java:15)
Caused by: java.net.ConnectException: Connection timed out: connect
at java.net.DualStackPlainSocketImpl.connect0(Native Method)
at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.SocksSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.(Unknown Source)
at java.net.Socket.(Unknown Source)
at org.postgresql.core.PGStream.(PGStream.java:60)
at
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:74)
... 11 more
I am also sets
postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
host all all 10.10.10.100/32 trust
# IPv6 local connections:
host all all 0.0.0.0/0 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
# host replication postgres 127.0.0.1/32 md5
# host replication postgres ::1/128 md5
Please give me a solution
Thanks & Regards
Arun R T
Re: [GENERAL] Strange dump/restore effect
Try just loading the 1.5 dump directly into the 2.0 database without the filtering step. It will be immensely noisy with lots of errors and warnings, but with luck you should find your data is there waiting for you when it's done. P On Sun, Sep 23, 2012 at 2:37 PM, Gražvydas Valeika wrote: > Hi all, > > I just migrated to 9.2 and observing stange thing. > > While restoring 9.2 database to another server's empty database I'm getting > several errors while restoring views: > > pg_restore: creating RULE _RETURN > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 5466; 2618 26660 RULE > _RETURN postgres > pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at > or near ")" > LINE 2: ALTER VIEW v_vehicle SET (); > ^ > Command was: CREATE RULE "_RETURN" AS ON SELECT TO v_vehicle DO INSTEAD > SELECT v.vehicle_id, v.vehicle_code, v.home_location_id, v.vehicl... > pg_restore: creating RULE _RETURN > pg_restore: [archiver (db)] Error from TOC entry 5487; 2618 26835 RULE > _RETURN postgres > pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at > or near ")" > LINE 2: ALTER VIEW v_r_delivery SET (); > > There is lot of views, other are backuped/restored without problems. > Those several views which are restored with errors are visible in new > database as empty tables. > > Source database is produced by PostGIS 2.0 migration script from 9.1/PostGIS > 1.5 database backup. Attempt to drop those strange views, recreate them and > then run dump/restore doesn't change anything. > > > Same backup/restore procedure worked fine in 9.1. > > Any ideas? > > Grazvydas > > -- 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] Prolem to acess PostgeSQL from other mechine
And the IP address of the machine you are trying to connect from is?
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Arun R T
Sent: Sunday, September 23, 2012 11:51 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Prolem to acess PostgeSQL from other mechine
Hi
I have some problem to connect PostgeSQL server from client mechine, error
and code is given below. I am using Windows 7 OS
Code
public static void main(String[] args) {
try {
System.out.println("Connecting..");
Class.forName("org.postgresql.Driver").newInstance();
connection =
DriverManager.getConnection("jdbc:postgresql://10.10.10.101:6060/mydb",
"postgres", "welcome");
} catch (Exception e) {
e.printStackTrace();
}
}
Error
org.postgresql.util.PSQLException: Connection refused. Check that the
hostname and port are correct and that the postmaster is accepting TCP/IP
connections.
at
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFa
ctoryImpl.java:136)
at
org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:
64)
at
org.postgresql.jdbc2.AbstractJdbc2Connection.(AbstractJdbc2Connection.
java:123)
at
org.postgresql.jdbc3.AbstractJdbc3Connection.(AbstractJdbc3Connection.
java:28)
at
org.postgresql.jdbc3g.AbstractJdbc3gConnection.(AbstractJdbc3gConnecti
on.java:20)
at
org.postgresql.jdbc4.AbstractJdbc4Connection.(AbstractJdbc4Connection.
java:30)
at org.postgresql.jdbc4.Jdbc4Connection.(Jdbc4Connection.java:22)
at org.postgresql.Driver.makeConnection(Driver.java:391)
at org.postgresql.Driver.connect(Driver.java:265)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at com.web.view.TestLogic.main(TestLogic.java:15)
Caused by: java.net.ConnectException: Connection timed out: connect
at java.net.DualStackPlainSocketImpl.connect0(Native Method)
at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.SocksSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.(Unknown Source)
at java.net.Socket.(Unknown Source)
at org.postgresql.core.PGStream.(PGStream.java:60)
at
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFa
ctoryImpl.java:74)
... 11 more
I am also sets
postgresql.conf
listen_addresses = '*'# what IP address(es) to listen on;
# comma-separated list of addresses;
pg_hba.conf
# TYPE DATABASEUSERADDRESS METHOD
# IPv4 local connections:
host all all 10.10.10.100/32 trust
# IPv6 local connections:
host all all 0.0.0.0/0 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
# host replication postgres 127.0.0.1/32 md5
# host replication postgres ::1/128 md5
Please give me a solution
Thanks & Regards
Arun R T
[GENERAL] In one of negative test row-level trigger results into loop
Below test results into Loop:
1.create test table
CREATE TABLE TEST_TABLE (NAME VARCHAR2, AGE INT);
2.create trigger function
CREATE OR REPLACE FUNCTION TRIG_FUNC () RETURNS TRIGGER AS
$$
DECLARE
PSQL VARCHAR2;
BEGIN
Raise info 'This is Test!!!';
psql:= 'INSERT INTO TEST_TABLE VALUES(''john'', 25);';
execute psql;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
3.create trigger
CREATE TRIGGER TEST_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON TEST_TABLE
FOR EACH ROW
EXECUTE PROCEDURE TRIG_FUNC ();
4.Perform an insert statement
INSERT INTO TEST_TABLE VALUES（'jack',25);
Now, You will see an always loop.
I understand that user can change his code to make it proper.
However shouldn’t PostgreSQL also throws errors in such cases for recursion
level or something related?
With Regards,
Amit Kapila.
Re: [GENERAL] In one of negative test row-level trigger results into loop
Amit Kapila writes: > Below test results into Loop: > [ AFTER INSERT trigger does another insert into its target table ] Well, of course. The INSERT results in scheduling another AFTER event. > I understand that user can change his code to make it proper. > However shouldn$B!G(Bt PostgreSQL also throws errors in such cases for > recursion > level or something related? No. In the first place, there is no recursion here: the triggers fire sequentially, not in a nested way. In the second place, this sort of thing is not necessarily wrong --- it's okay for a trigger to do something like that, so long as it doesn't repeat it indefinitely. (A human can see that this function will never stop adding rows, but Postgres' trigger mechanism doesn't have that much insight.) In the third place, we don't attempt to prevent queries from taking unreasonable amounts of time, and a loop in a trigger is not very different from anything else in that line. Use statement_timeout if you're concerned about that type of mistake. 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] How to do a full-text search words within some proximity of each other?
I noticed in elastic search (ES), you can do queries like "a b"~4 I think this query will match stuff like "a b" and "a x x b" but not something like "a x x x x x x x x b". I'm not sure if this kind of thing is possible with postgresql full text search. Is it possible? I understand that I can do a query and rank the results by how closely the words are to each other, but I want to exclude any matches where the words are not within two words of each other. Thanks in advance! Matt -- W. Matthew Wilson m...@tplus1.com http://tplus1.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] Prolem to acess PostgeSQL from other mechine
On 09/23/12 20:50, Arun R T wrote: > > I have some problem to connect PostgeSQL server from client mechine [snip] > org.postgresql.util.PSQLException: Connection refused. Check that the hostname > and port are correct and that the postmaster is accepting TCP/IP connections. Given this ^^^ follow the recommendations: Check that you are talking to the correct host (10.10.10.101). Then check that you have the right port number (you had 6060 but you didn't mention that you had changed it from the default which is usually 5432 for the first PG instance on a machine). You indicated that you had enabled listening for tcp on all addresses. Did you (re)start the server afterwards? As a final act of desperation, make sure there is no intervening firewalls/routers that may be rejecting it. HTH Bosco. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] N-tile function in postgres
Hi, Can anyone help me with an aggregate query I am having trouble with? I want to get the top 5 or top 10 most frequently shopped in merchant categories for each account holder at a bank and put each of the quintiles/deciles into separate columns. I would also like to put the average transaction amount for each of those top 5-10 categories into separate columns, and the date of the last transaction in each of those 5 to 10 categories into separate columns. I am told that ntile may be an option for doing this, but can't find any examples for using it in the documentation. We use Postgresql 9.1. Thank you so much for your help. Rachel
Re: [GENERAL] Prolem to acess PostgeSQL from other mechine
On 09/23/2012 08:50 PM, Arun R T wrote:
Hi
I have some problem to connect PostgeSQL server from client mechine,
error and code is given below. I am using Windows 7 OS
Code
public static void main(String[] args) {
try {
System.out.println("Connecting..");
Class.forName("org.postgresql.Driver").newInstance();
connection =
DriverManager.getConnection("jdbc:postgresql://10.10.10.101:6060/mydb",
"postgres", "welcome");
} catch (Exception e) {
e.printStackTrace();
}
}
Error
org.postgresql.util.PSQLException: Connection refused. Check that the
hostname and port are correct and that the postmaster is accepting
TCP/IP connections.
at
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:136)
at
org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
at
org.postgresql.jdbc2.AbstractJdbc2Connection.(AbstractJdbc2Connection.java:123)
at
org.postgresql.jdbc3.AbstractJdbc3Connection.(AbstractJdbc3Connection.java:28)
at
org.postgresql.jdbc3g.AbstractJdbc3gConnection.(AbstractJdbc3gConnection.java:20)
at
org.postgresql.jdbc4.AbstractJdbc4Connection.(AbstractJdbc4Connection.java:30)
at org.postgresql.jdbc4.Jdbc4Connection.(Jdbc4Connection.java:22)
at org.postgresql.Driver.makeConnection(Driver.java:391)
at org.postgresql.Driver.connect(Driver.java:265)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at com.web.view.TestLogic.main(TestLogic.java:15)
Caused by: java.net.ConnectException: Connection timed out: connect
at java.net.DualStackPlainSocketImpl.connect0(Native Method)
at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.SocksSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.(Unknown Source)
at java.net.Socket.(Unknown Source)
at org.postgresql.core.PGStream.(PGStream.java:60)
at
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:74)
... 11 more
I am also sets
postgresql.conf
listen_addresses = '*'# what IP address(es) to listen on;
# comma-separated list of addresses;
pg_hba.conf
# TYPE DATABASEUSERADDRESS METHOD
# IPv4 local connections:
host all all 10.10.10.100/32 trust
# IPv6 local connections:
host all all 0.0.0.0/0 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
# host replication postgres 127.0.0.1/32 md5
# host replication postgres ::1/128 md5
Please give me a solution
Did you restart the server after making the above changes?
A restart is needed for the listen_address change to be seen.
Its unclear where the server is running. Is it running on Windows or is
the client running on Windows? In either case is there a firewall
between the two that could be preventing a connection?
Thanks & Regards
*Arun R T*
--
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] In one of negative test row-level trigger results into loop
On 09/24/2012 07:03 AM, Amit Kapila wrote:
> Below test results into Loop:
>
> 1.create test table
>
> CREATE TABLE TEST_TABLE (NAME VARCHAR2, AGE INT);
>
> 2.create trigger function
>
> CREATE OR REPLACE FUNCTION TRIG_FUNC () RETURNS TRIGGER AS
>
> $$
>
> DECLARE
>
> PSQL VARCHAR2;
>
> BEGIN
>
> Raise info 'This is Test!!!';
>
> psql:= 'INSERT INTO TEST_TABLE VALUES(''john'', 25);';
>
> execute psql;
>
> RETURN NEW;
>
> END;
>
> $$ LANGUAGE plpgsql;
>
> 3.create trigger
>
> CREATE TRIGGER TEST_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON
> TEST_TABLE FOR EACH ROW
>
> EXECUTE PROCEDURE TRIG_FUNC ();
>
> 4.Perform an insert statement
>
> INSERT INTO TEST_TABLE VALUES（'jack',25);
>
> Now, You will see an always loop.
>
> I understand that user can change his code to make it proper.
>
> However shouldn’t PostgreSQL also throws errors in such cases for
> recursion level or something related?
What database are you running this on?
I get :
test=> CREATE TABLE TEST_TABLE (NAME VARCHAR2, AGE INT);
ERROR: type "varchar2" does not exist
LINE 1: CREATE TABLE TEST_TABLE (NAME VARCHAR2, AGE INT);
>
> With Regards,
>
> Amit Kapila.
>
--
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] Running CREATE only on certain Postgres versions
On Mon, Sep 24, 2012 at 2:32 PM, Robert James wrote:
> I have some code which creates a function in Postgres, taken from
> http://wiki.postgresql.org/wiki/Array_agg .
>
> DROP AGGREGATE IF EXISTS array_agg(anyelement);
> CREATE AGGREGATE array_agg(anyelement) (
> SFUNC=array_append,
> STYPE=anyarray,
> INITCOND='{}'
> );
>
> The function was added in 8.4, and so the code fails when run on 8.4 or
> higher.
>
> How can I make the code cross-version compatible? For instance, how
> can I tell it to check the version, and only run if 8.3 or lower? Or
> another way to make it cross-version?
You could create a plpgsql function that tries to creates the object
catching the exception, then call the function and drop it. Something
like the following (untested):
create function try_to_create_aggregate() language plpgsql as $$
begin
begin
execute $agg$
DROP AGGREGATE IF EXISTS array_agg(anyelement);
CREATE AGGREGATE array_agg(anyelement) (
...
$agg$
exception
see here to know how to handle
http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
end;
end
$$;
select try_to_create_aggregate();
drop function try_to_create_aggregate();
In more recent postgres versions you can use "do" avoiding to create
the function.
-- Daniele
--
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] N-tile function in postgres
Le 2012-09-24 à 12:32, Rachel Owsley a écrit : > Hi, > > Can anyone help me with an aggregate query I am having trouble with? > > I want to get the top 5 or top 10 most frequently shopped in merchant > categories for each account holder at a bank and put each of the > quintiles/deciles into separate columns. I would also like to put the average > transaction amount for each of those top 5-10 categories into separate > columns, and the date of the last transaction in each of those 5 to 10 > categories into separate columns. I am told that ntile may be an option for > doing this, but can’t find any examples for using it in the documentation. > > We use Postgresql 9.1. > > Thank you so much for your help. > > Rachel Hi! Look at the tablefunc extension to do cross tabulation. The crosstab family of functions turn a series of rows into columns. Something like this: a | 1 b | 2 a | b 1 | 2 It obviously works with more columns. That would take care of the final part of your query. I've never used ntile() myself, but the docs say it returns 1 to the value. Then you may want the min/max amount per decile to extract the values you want. Something like this (untested, made up schema): WITH raw_values( SELECT account_id , merchant_category_id , amount FROM transactions JOIN merchants USING (merchant_id)) , partitioned_sales AS ( SELECT account_id , merchant_category_id , ntile(10) over (partition by account_id, merchant_category_id order by amount) as "partition" , min(amount) over (partition by account_id, merchant_category_id order by amount) as amount FROM raw_values) SELECT * FROM partitioned_sales ORDER BY account_id, merchant_category_id, partition, amount Hope that helps! François
Re: [GENERAL] N-tile function in postgres
Thank you, François! This is very helpful! I'll give this query a try. I don't know the cross-tab function, but that's exactly what I want to do for the column output. Regarding the sample query, I see the min (amount), but how is the upper bound defined for each decile? Thanks, Rachel From: François Beausoleil [mailto:franc...@teksol.info] Sent: Monday, September 24, 2012 12:57 PM To: Rachel Owsley Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] N-tile function in postgres Le 2012-09-24 à 12:32, Rachel Owsley a écrit : Hi, Can anyone help me with an aggregate query I am having trouble with? I want to get the top 5 or top 10 most frequently shopped in merchant categories for each account holder at a bank and put each of the quintiles/deciles into separate columns. I would also like to put the average transaction amount for each of those top 5-10 categories into separate columns, and the date of the last transaction in each of those 5 to 10 categories into separate columns. I am told that ntile may be an option for doing this, but can't find any examples for using it in the documentation. We use Postgresql 9.1. Thank you so much for your help. Rachel Hi! Look at the tablefunc extension to do cross tabulation. The crosstab family of functions turn a series of rows into columns. Something like this: a | 1 b | 2 a | b 1 | 2 It obviously works with more columns. That would take care of the final part of your query. I've never used ntile() myself, but the docs say it returns 1 to the value. Then you may want the min/max amount per decile to extract the values you want. Something like this (untested, made up schema): WITH raw_values( SELECT account_id , merchant_category_id , amount FROM transactions JOIN merchants USING (merchant_id)) , partitioned_sales AS ( SELECT account_id , merchant_category_id , ntile(10) over (partition by account_id, merchant_category_id order by amount) as "partition" , min(amount) over (partition by account_id, merchant_category_id order by amount) as amount FROM raw_values) SELECT * FROM partitioned_sales ORDER BY account_id, merchant_category_id, partition, amount Hope that helps! François
Re: [GENERAL] Running CREATE only on certain Postgres versions
> -Original Message-
> From: Robert James [mailto:srobertja...@gmail.com]
> Sent: Monday, September 24, 2012 9:33 AM
> To: Postgres General
> Subject: Running CREATE only on certain Postgres versions
>
> I have some code which creates a function in Postgres, taken from
> http://wiki.postgresql.org/wiki/Array_agg .
>
> DROP AGGREGATE IF EXISTS array_agg(anyelement); CREATE AGGREGATE
> array_agg(anyelement) ( SFUNC=array_append, STYPE=anyarray,
> INITCOND='{}'
> );
>
> The function was added in 8.4, and so the code fails when run on 8.4 or
> higher.
>
> How can I make the code cross-version compatible? For instance, how
> can I tell it to check the version, and only run if 8.3 or lower? Or
> another way to make it cross-version?
Find your PG version with:
SELECT version();
and continue accordingly...
Regards,
Igor Neyman
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Custom prompt
Good afternoon, When accessing PostgreSQL via psql, is it possible to make use of a custom prompt? I would like something like postgres=# instead of just postgres=#. My search was fruitless (see next paragraph). And if I'm allowed two questions in one post, how does one search for a phrase such as "custom prompt" in the mailing list archives? Everything I tried found every page with either the word custom, the word prompt, or both words somewhere in the message. I just wanted to search for messages containing the exact phrase "custom prompt". Thanks, Craig Sent - Gtek Web Mail -- 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] Custom prompt
On 09/24/2012 11:20 AM, cr...@gtek.biz wrote: Good afternoon, When accessing PostgreSQL via psql, is it possible to make use of a custom prompt? I would like something like postgres=# instead of just postgres=#. My search was fruitless (see next paragraph). Should have added: To make it stick between sessions you can use a psqlrc file. They are handy for customizing psql in general. These can be system wide or per user. For more information do a find(psqlrc) on the page I previously linked to. And if I'm allowed two questions in one post, how does one search for a phrase such as "custom prompt" in the mailing list archives? Everything I tried found every page with either the word custom, the word prompt, or both words somewhere in the message. I just wanted to search for messages containing the exact phrase "custom prompt". Not sure about that mailing list, but using custom prompt in the search box in the docs finds the page I posted. Thanks, Craig Sent - Gtek Web Mail -- 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] Custom prompt
>> When accessing PostgreSQL via psql, is it possible to make use of a custom >> prompt? I would like something like postgres=# instead of just postgres=#. My >> search was fruitless (see next paragraph). > > Should have added: > To make it stick between sessions you can use a psqlrc file. They are > handy for customizing psql in general. These can be system wide or per > user. For more information do a find(psqlrc) on the page I previously > linked to. Aha, exactly what I was looking for. Thanks! >> >> And if I'm allowed two questions in one post, how does one search for a >> phrase >> such as "custom prompt" in the mailing list archives? Everything I tried >> found >> every page with either the word custom, the word prompt, or both words >> somewhere >> in the message. I just wanted to search for messages containing the exact >> phrase >> "custom prompt". > > Not sure about that mailing list, but using custom prompt in the search > box in the docs finds the page I posted. It does, but not by that exact phrase. It returns 10 hits, one of which is a page that contains the word "prompts", and the word "customize": ...prompts psql issues can be customized to your preference. The three variables PROMPT1 PROMPT2 , and PROMPT3... I'd like to return results that contain the exact phrase "custom prompt", not a lucky hit on similar words that are physically close to each other. Usually enclosing the phrase in quotes will do it, but not in any PostgreSQL search form that I've found. Sent - Gtek Web Mail -- 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] Custom prompt
On 09/24/2012 11:20 AM, cr...@gtek.biz wrote: > Good afternoon, > > When accessing PostgreSQL via psql, is it possible to make use of a custom > prompt? I would like something like postgres=# instead of just postgres=#. My > search was fruitless (see next paragraph). http://www.postgresql.org/docs/9.2/interactive/app-psql.html#APP-PSQL-PROMPTING > > And if I'm allowed two questions in one post, how does one search for a > phrase such as "custom prompt" in the mailing list archives? Everything I > tried found every page with either the word custom, the word prompt, or both > words somewhere in the message. I just wanted to search for messages > containing the exact phrase "custom prompt". > > Thanks, > Craig > > > Sent - Gtek Web Mail > > > > -- 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] Custom prompt
> > Try along the lines of > > psql -h some.host.com -U postgres -v "PROMPT1=*my_cool_prompt%/> " -d my_db > Works like a charm! Do I have a dot file that I can save this setting to, similar to the .exrc for vi, or will I need to enter it every time? If the latter, I'll alias it, but I'd rather be able to save it if possible. Thanks! Sent - Gtek Web Mail -- 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] Custom prompt
On 9/24/12 12:20 PM, cr...@gtek.biz wrote: Good afternoon, When accessing PostgreSQL via psql, is it possible to make use of a custom prompt? I would like something like postgres=# instead of just postgres=#. My search was fruitless (see next paragraph). And if I'm allowed two questions in one post, how does one search for a phrase such as "custom prompt" in the mailing list archives? Everything I tried found every page with either the word custom, the word prompt, or both words somewhere in the message. I just wanted to search for messages containing the exact phrase "custom prompt". Thanks, Craig Sent - Gtek Web Mail Try along the lines of psql -h some.host.com -U postgres -v "PROMPT1=*my_cool_prompt%/> " -d my_db no se on the second Q. ds -- 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] Custom prompt
Apologies to all, I didn't realize this ISPs webmail client doesn't wrap lines. My reply, formatted in a more readable way: On Monday, September 24, 2012 14:07, cr...@gtek.biz said: >> When accessing PostgreSQL via psql, is it possible to make use of a custom >> prompt? I would like something like postgres=# instead of just postgres=#. My >> search was fruitless (see next paragraph). > > Should have added: > To make it stick between sessions you can use a psqlrc file. They are > handy for customizing psql in general. These can be system wide or per > user. For more information do a find(psqlrc) on the page I previously > linked to. Aha, exactly what I was looking for. Thanks! >> >> And if I'm allowed two questions in one post, how does one search for a >> phrase >> such as "custom prompt" in the mailing list archives? Everything I tried >> found >> every page with either the word custom, the word prompt, or both words >> somewhere >> in the message. I just wanted to search for messages containing the exact >> phrase >> "custom prompt". > > Not sure about that mailing list, but using custom prompt in the search > box in the docs finds the page I posted. It does, but not by that exact phrase. It returns 10 hits, one of which is a page that contains the word "prompts", and the word "customize": ...prompts psql issues can be customized to your preference. The three variables PROMPT1 PROMPT2 , and PROMPT3... I'd like to return results that contain the exact phrase "custom prompt", not a lucky hit on similar words that are physically close to each other. Usually enclosing the phrase in quotes will do it, but not in any PostgreSQL search form that I've found. Sent - Gtek Web Mail -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to do a full-text search words within some proximity of each other?
something like this ? http://www.sai.msu.su/~megera/wiki/2009-08-12 http://www.sai.msu.su/~megera/postgres/talks/algebra-fts.pdf Unfortunately, we get no support for this work, so we stop maintaining phrase-search patch. I even thinking about kikstarter.com to get money for this project :) Oleg On Mon, 24 Sep 2012, W. Matthew Wilson wrote: I noticed in elastic search (ES), you can do queries like "a b"~4 I think this query will match stuff like "a b" and "a x x b" but not something like "a x x x x x x x x b". I'm not sure if this kind of thing is possible with postgresql full text search. Is it possible? I understand that I can do a query and rank the results by how closely the words are to each other, but I want to exclude any matches where the words are not within two words of each other. Thanks in advance! Matt Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] N-tile function in postgres
Le 2012-09-24 à 14:12, Rachel Owsley a écrit : > Thank you, François! This is very helpful! I’ll give this query a try. I > don’t know the cross-tab function, but that’s exactly what I want to do for > the column output. Regarding the sample query, I see the min (amount), but > how is the upper bound defined for each decile? ntile() splits the output in as even partitions as possible. If you have 13 rows, and you want 10 output rows, then each row will receive something like this: # select id, ntile(10) over () from generate_series(1, 13) as t1(id); id | ntile +--- 1 | 1 2 | 1 3 | 2 4 | 2 5 | 3 6 | 3 7 | 4 8 | 5 9 | 6 10 | 7 11 | 8 12 | 9 13 |10 The ntile() function isn't tied to the values at all: only to the actual number of rows. I used min(amount) to get the minimal value per group, but you can use use max(amount) to get the other end as well. Bye! François
Re: [GENERAL] N-tile function in postgres
Thank you, François!! Got it. :) From: François Beausoleil [mailto:franc...@teksol.info] Sent: Monday, September 24, 2012 3:37 PM To: Rachel Owsley Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] N-tile function in postgres Le 2012-09-24 à 14:12, Rachel Owsley a écrit : Thank you, François! This is very helpful! I'll give this query a try. I don't know the cross-tab function, but that's exactly what I want to do for the column output. Regarding the sample query, I see the min (amount), but how is the upper bound defined for each decile? ntile() splits the output in as even partitions as possible. If you have 13 rows, and you want 10 output rows, then each row will receive something like this: # select id, ntile(10) over () from generate_series(1, 13) as t1(id); id | ntile +--- 1 | 1 2 | 1 3 | 2 4 | 2 5 | 3 6 | 3 7 | 4 8 | 5 9 | 6 10 | 7 11 | 8 12 | 9 13 |10 The ntile() function isn't tied to the values at all: only to the actual number of rows. I used min(amount) to get the minimal value per group, but you can use use max(amount) to get the other end as well. Bye! François
[GENERAL] What am I doing wrong?
I'm in the single-slave scenario, with hot standby capabilities, meaning I want to run queries on the slave. I'm running some tests to evaluate pgbarman, on Ubuntu 11.10. I used only packaged PostgreSQL, and I'm running version "PostgreSQL 9.1.5 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit". Both the master and the slave are running on the same host. master/postgresql.conf port = 5432 archive_mode = on wal_level = hot_standby max_wal_senders = 3 wal_keep_segments = 256 archive_command = '/bin/cp --verbose %p /var/pgexchange/%f' master/pg_hba.conf (as I said, testing config only): hostreplication postgres127.0.0.1/32trust slave/postgrseql.conf: port = 5433 hot_standby = on hot_standby_feedback = on max_standby_archive_delay = -1 max_standby_streaming_delay = -1 slave/pg_hba.conf -- all at default /var/lib/postgresql/9.1/slave0/recovery.conf: standby_mode = on restore_command = '/bin/cp --verbose /var/pgexchange/%f %p' primary_conninfo = 'host=localhost port=5432 user=postgres password=supersecretpassword' The slave's log says it's connected to the master, but I can't connect. # psql -h localhost -p 5433 -U postgres -d mydb psql: FATAL: the database system is starting up FATAL: the database system is starting up The slave's log, after a fresh pg_basebackup + restore for the slave, contains: ==> /var/log/postgresql/postgresql-9.1-slave0.log <== 2012-09-25 00:46:22 UTC LOG: database system was interrupted; last known up at 2012-09-25 00:44:20 UTC 2012-09-25 00:46:22 UTC LOG: creating missing WAL directory "pg_xlog/archive_status" 2012-09-25 00:46:22 UTC LOG: entering standby mode `/var/pgexchange/00010016' -> `pg_xlog/RECOVERYXLOG' 2012-09-25 00:46:22 UTC LOG: restored log file "00010016" from archive 2012-09-25 00:46:23 UTC LOG: redo starts at 0/1620 2012-09-25 00:46:23 UTC LOG: consistent recovery state reached at 0/1700 /bin/cp: cannot stat `/var/pgexchange/00010017': No such file or directory 2012-09-25 00:46:23 UTC LOG: incomplete startup packet 2012-09-25 00:46:23 UTC LOG: streaming replication successfully connected to primary 2012-09-25 00:46:23 UTC FATAL: the database system is starting up 2012-09-25 00:46:24 UTC FATAL: the database system is starting up 2012-09-25 00:46:24 UTC FATAL: the database system is starting up The "system is starting up" are the result of the pg_ctlcluster script which attempts to connect to the database to check if the server's up and available. According to the log above, a consistent state is reached, and the slave connects to the primary. During the slave's reconnection, the master emits no messages. On the master, pg_stat_replication looks fine: # select * from pg_stat_replication ; procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state -+--+--+--+-+-+-+---+---+---+++-+---+ 27920 | 10 | postgres | walreceiver | 127.0.0.1 | | 52193 | 2012-09-25 00:46:23.100631+00 | streaming | 0/1700| 0/1700 | 0/1700 | 0/1700 | 0 | async state == streaming; sent == write == flush == replay, so the slave seems to be consistent. What am I missing here? Thanks! François -- 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] Running CREATE only on certain Postgres versions
Unfortunately, SELECT VERSION() gives a long text string - parsing out
the version isn't reliable.
So, we can reduce my question to a simpler question: What's the best
way to determine if postgres is running > version x?
Or, what's the best way to determine the exact version number
programatically (ie not just a long string)
On 9/24/12, Igor Neyman wrote:
>> -Original Message-
>> From: Robert James [mailto:srobertja...@gmail.com]
>> Sent: Monday, September 24, 2012 9:33 AM
>> To: Postgres General
>> Subject: Running CREATE only on certain Postgres versions
>>
>> I have some code which creates a function in Postgres, taken from
>> http://wiki.postgresql.org/wiki/Array_agg .
>>
>> DROP AGGREGATE IF EXISTS array_agg(anyelement); CREATE AGGREGATE
>> array_agg(anyelement) ( SFUNC=array_append, STYPE=anyarray,
>> INITCOND='{}'
>> );
>>
>> The function was added in 8.4, and so the code fails when run on 8.4 or
>> higher.
>>
>> How can I make the code cross-version compatible? For instance, how
>> can I tell it to check the version, and only run if 8.3 or lower? Or
>> another way to make it cross-version?
>
> Find your PG version with:
> SELECT version();
>
> and continue accordingly...
>
> Regards,
> Igor Neyman
>
--
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] Running CREATE only on certain Postgres versions
Server parameter: server_version_num
http://www.postgresql.org/docs/9.2/interactive/runtime-config-preset.html
David J.
On Sep 24, 2012, at 21:23, Robert James wrote:
> Unfortunately, SELECT VERSION() gives a long text string - parsing out
> the version isn't reliable.
>
> So, we can reduce my question to a simpler question: What's the best
> way to determine if postgres is running > version x?
>
> Or, what's the best way to determine the exact version number
> programatically (ie not just a long string)
>
> On 9/24/12, Igor Neyman wrote:
>>> -Original Message-
>>> From: Robert James [mailto:srobertja...@gmail.com]
>>> Sent: Monday, September 24, 2012 9:33 AM
>>> To: Postgres General
>>> Subject: Running CREATE only on certain Postgres versions
>>>
>>> I have some code which creates a function in Postgres, taken from
>>> http://wiki.postgresql.org/wiki/Array_agg .
>>>
>>> DROP AGGREGATE IF EXISTS array_agg(anyelement); CREATE AGGREGATE
>>> array_agg(anyelement) ( SFUNC=array_append, STYPE=anyarray,
>>> INITCOND='{}'
>>> );
>>>
>>> The function was added in 8.4, and so the code fails when run on 8.4 or
>>> higher.
>>>
>>> How can I make the code cross-version compatible? For instance, how
>>> can I tell it to check the version, and only run if 8.3 or lower? Or
>>> another way to make it cross-version?
>>
>> Find your PG version with:
>> SELECT version();
>>
>> and continue accordingly...
>>
>> Regards,
>> Igor Neyman
>>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpython2u not getting any output -> on independent script I get the desired output
Hello everybody and thanks for your attention.
I have this function:
###
CREATE OR REPLACE FUNCTION check_current_xlog() RETURNS
text
AS
$$
import subprocess
p = subprocess.Popen("ssh repuser@localhost -p 2000 \"psql -A -t -c
'select pg_current_xlog_location();' template1\"
",shell=True,stdout=subprocess.PIPE)
out,err = p.communicate()
return str(out)
$$
LANGUAGE plpython2u VOLATILE;
###
*Problem:* *I'm not getting any output*
select check_current_xlog();
check_current_xlog
(1 row)
I checked with plpy.notice(out) and out is empty
If I execute those instructions in a script like this one
##
#!/usr/bin/env python
import subprocess
p = subprocess.Popen("ssh repuser@localhost -p 2000 \"psql -A -t -c 'select
pg_current_xlog_location();' template1\"
",shell=True,stdout=subprocess.PIPE)
out,err = p.communicate()
print out
##
I get the desired output:
F/6CB78FC
---
Any ideas will be highly appreciated. Greetings from Mexico.
Re: [GENERAL] Timeline switch problem with streaming replication with 3 nodes
On Mon, Sep 24, 2012 at 7:37 PM, wrote: > I've found a discussion > (http://archives.postgresql.org/pgsql-general/2011-12/msg00553.php) on a > similar issue a while back. They talk about sharing WAL files as the > solution. But I thought that the idea with streaming replication was that I > would not need a shared storage. > > Is that the only solution or is there another way? Things should work if you manually copy across the 010.history file from the new master's pg_xlog directory to the slave's. This method isn't documented, but seems to work. I believe the problem is being fixed, by letting the history files be shipped along with the WAL files. http://archives.postgresql.org/pgsql-general/2011-12/msg00456.php -- Stuart Bishop http://www.stuartbishop.net/ -- 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] In one of negative test row-level trigger results into loop
> On Monday, September 24, 2012 7:44 PM Adrian Klaver
> On 09/24/2012 07:03 AM, Amit Kapila wrote:
> > Below test results into Loop:
> >
> > 1.create test table
> >
> > CREATE TABLE TEST_TABLE (NAME VARCHAR2, AGE INT);
> >
> > 2.create trigger function
> >
> > CREATE OR REPLACE FUNCTION TRIG_FUNC () RETURNS TRIGGER AS
> >
> > $$
> >
> > DECLARE
> >
> > PSQL VARCHAR2;
> >
> > BEGIN
> >
> > Raise info 'This is Test!!!';
> >
> > psql:= 'INSERT INTO TEST_TABLE VALUES(''john'', 25);';
> >
> > execute psql;
> >
> > RETURN NEW;
> >
> > END;
> >
> > $$ LANGUAGE plpgsql;
> >
> > 3.create trigger
> >
> > CREATE TRIGGER TEST_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON
> > TEST_TABLE FOR EACH ROW
> >
> > EXECUTE PROCEDURE TRIG_FUNC ();
> >
> > 4.Perform an insert statement
> >
> > INSERT INTO TEST_TABLE VALUES（'jack',25);
> >
> > Now, You will see an always loop.
> >
> > I understand that user can change his code to make it proper.
> >
> > However shouldn’t PostgreSQL also throws errors in such cases for
> > recursion level or something related?
>
> What database are you running this on?
> I get :
> test=> CREATE TABLE TEST_TABLE (NAME VARCHAR2, AGE INT);
> ERROR: type "varchar2" does not exist
> LINE 1: CREATE TABLE TEST_TABLE (NAME VARCHAR2, AGE INT);
>
I am sorry, actually I was trying to compare behavior with Oracle so used it
wrongly.
In Create Table statement, change Varchar2 to Varchar(30)
And change in trigger function from Varchar2 to Varchar(200)
With Regards,
Amit Kapila.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
