Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

On Tue, Jun 19, 2012 at 02:20:57AM -0400, Tom Lane wrote: > Craig Ringer writes: > > I've been working in psql a lot recently, and have started to wonder why > > statements with syntax errors or other problems that render them > > unexecutable terminate the transaction. > > Well, the obvious re

Re: [GENERAL] How to include Tablefunc as an extension

On Mon, Jun 18, 2012 at 11:34 PM, Stefan Schwarzer wrote: > >>> I read through the Postgres doc and many Google results, but it seems >>> still unclear to me how to include additional packages into my postgres >>> database. I see that there are a few installed under >>> "/usr/local/pgsql-9.1/share

[GENERAL] pgstat wait timeout : permission denied

Hi All my configuration: (-) postgres 9.1 (-) windows 2003 (-) 4 Xeon CPUs (-) 16GB of RAM I'm getting a lot of errors in my postgres log file: --- 2012-06-19 09:31:06 CESTLOCATION: backend_read_statsfile, .\src\backend\postmaster\pgstat.c:3807 2012-06-19 09:31:38 CES

Re: [GENERAL] Error message "psql: could not connect to server: No such file or directory"

>>> I guess this is a typical user error. I searched around to find the >>> solution, but in vain. >>> >>> I just upgraded to Lion, and used Kyngchaos libraries for installation of >>> Postgres. >>> >>> Try to get running postgres, and I get this: >>> >>> /usr/local/pgsql-9.1/bin/psql -U

Re: [GENERAL] Fine-grained replication?

On Tue, Jun 19, 2012 at 1:49 AM, Paul Jones wrote: > Is anyone aware of other non-trigger-based, fine-grained replication tools > for PostgreSQL along the lines of the XReader > or pgreplay > ? Binary Replicati

Re: [GENERAL] pgstat wait timeout : permission denied

> 2012-06-19 09:33:38 CESTWARNING:  01000: pgstat wait timeout > 2012-06-19 09:33:38 CESTLOCATION:  backend_read_statsfile, > .\src\backend\postmaster\pgstat.c:3807 > 2012-06-19 09:33:41 CESTLOG:  42501: could not rename temporary statistics > file "pg_stat_tmp/pgstat.tmp" to "pg_stat_tmp/pgstat.st

[GENERAL] Please make it easy to drop a database that is in use

Hi All, When I'm developing against a PostgreSQL database I often drop and re-create it and I often find that the drop fails, because it's "in use by other users". This is really annoying, especially when I know full well there are no other users - it's just me. I'm aware of the workaround:

Re: [GENERAL] Fine-grained replication?

On Mon, 18 Jun 2012 14:49:32 -0700 (PDT), Paul Jones wrote: > Is anyone aware of other non-trigger-based, fine-grained replication tools > for PostgreSQL along the lines of the XReader > or pgreplay > ? > If you

Re: [GENERAL] pgstat wait timeout : permission denied

hmm, interesting idea. I'll check, it will be a bit difficult because it is centrally managed by administrators, but I will see how to temporary disable it. Thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-permission-denied-tp5713236p5713255.htm

Re: [GENERAL] Fine-grained replication?

Paul Jones wrote: > Is anyone aware of other non-trigger-based, fine-grained replication tools for PostgreSQL along the > lines of the XReader or pgreplay > ? I'm not sure what you want, because "pgreplay" is certa

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

On 06/19/2012 02:20 PM, Tom Lane wrote: So you're suggesting that "SELECT 1/0;" should terminate a transaction, but "SELECT 1//0;" should not? How about "ROLBACK;"? It gets pretty squishy pretty fast when you try to decide which sorts of errors are more important than others. When put that way

Re: [GENERAL] Fine-grained replication?

On Τρι 19 Ιουν 2012 13:09:28 Guillaume Lelarge wrote: > On Mon, 18 Jun 2012 14:49:32 -0700 (PDT), Paul Jones > > wrote: > > Is anyone aware of other non-trigger-based, fine-grained replication > > tools > > > for PostgreSQL along the lines of the XReader > >

Re: [GENERAL] Fine-grained replication?

On Τρι 19 Ιουν 2012 12:00:49 Sergey Konoplev wrote: > On Tue, Jun 19, 2012 at 1:49 AM, Paul Jones wrote: > > Is anyone aware of other non-trigger-based, fine-grained replication > > tools for PostgreSQL along the lines of the XReader > > or pgreplay > >

[GENERAL] SELECT INTO create new table, how to turn off this feature?

Hi, Is it possible to turn off future "SELECT INTO" that create new table? I use it on pl/pgsql function, and I have mamy mistakes with variable name in SQL thanks Wojtek -- View this message in context: http://postgresql.1045698.n5.nabble.com/SELECT-INTO-create-new-table-how-to-turn-off-this-

[GENERAL] How to get no. of commits/rollbacks by application on the database?

Hi All, How can we figure out no. of commit/rollback happend on the database by application ? As per my testing what I observed, even if your database is idle than in pg_stat_database (xact_commit/xact_rollback) columns keep increasing. This means autovacuum/autoanalyze count also included in the

Re: [GENERAL] SELECT INTO create new table, how to turn off this feature?

On 19/06/2012 14:05, aasat wrote: > Hi, > > Is it possible to turn off future "SELECT INTO" that create new table? > I use it on pl/pgsql function, and I have mamy mistakes with variable name > in SQL No, it's not possible. What problems are you running into? Ray. -- Raymond O'Donnell :: Ga

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

On Tue, 2012-06-19 at 19:06 +0800, Craig Ringer wrote: > On 06/19/2012 02:20 PM, Tom Lane wrote: > > So you're suggesting that "SELECT 1/0;" should terminate a transaction, > > but "SELECT 1//0;" should not? How about "ROLBACK;"? It gets pretty > > squishy pretty fast when you try to decide which

Re: [GENERAL] SELECT INTO create new table, how to turn off this feature?

Sorry, my mistake, Postgres 9.0 for unexists variables throwing error "myvariable" is not a known variable -- View this message in context: http://postgresql.1045698.n5.nabble.com/SELECT-INTO-create-new-table-how-to-turn-off-this-feature-tp5713267p5713279.html Sent from the PostgreSQL - general m

Re: [GENERAL] Error message "psql: could not connect to server: No such file or directory"

On 06/19/2012 01:32 AM, Stefan Schwarzer wrote: I need unfortunately to come back to this issue. I (again) re-installed Lion from scratch, and finally got everything working. The Postgres was running, I uploaded a couple of dumped SQL files. And then re-started the machine for another reason

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

On Tue, Jun 19, 2012 at 03:35:19PM +0200, Rafal Pietrak wrote: > > The point is, that SQL syntax errors are so obviusly different from > execution errors, that noting this distinction should not raise any > ambiguity. Good. One looks forward to your fully-worked-out AI/ESP patch that gets this r

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

Rafal Pietrak writes: > The point is, that SQL syntax errors are so obviusly different from > execution errors, that noting this distinction should not raise any > ambiguity. I beg to disagree. Typos can manifest themselves as execution errors just as well as syntax errors. You are probably thi

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

Hm, sorry but I still can not get into that argument. Take your example 3 (COMINT in place of COMMIT) How should the DB know that (and how) to safely recover from such error? You need to tell - and there are tools to do so right available. In an interactive session: - use "autocommit=on" to indi

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing... Tom Lane escreveu: >Rafal Pietrak writes: >> The point is, that SQL syntax errors are so o

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 ... > ON_ERROR_ROLLBACK=interactive provides a helper for that in psql. > > Savepoints are overhead, though, and I don't understand why they're > required for statements that don't even parse. Other have handled the latter part of the above al

[GENERAL] db server processes hanging around

hi we are running out of database connections. we are using pg 9.0.6 on linux centos 5.7 64bit. we are not using any go-between connection pools such as pgbouncer or pgpool - connections occur directly from client to database. the connection setup on the client (java) is default, only providing (

Re: [GENERAL] Smaller multiple tables or one large table?

Hi all, I am curious if there is a significant speed up with doing this if most of the queries run against it are going to be table wide. I won't drop the data and the data won't really grow. Do I get better speedup with one large table and large indexes or many small tables with many small indexe

Re: [GENERAL] Fine-grained replication?

I should have been more specific about why I'm interested in this. The idea is not so much the replication but being able to use the replication information to make business decisions based on what is observed happening to the data, or being able to selectively repair data if there are problems.

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter wrote: > There is also the case of dynamically generated sql statements based on user > selection... being syntax or not, I would never want half job done. Thia is > the purpose of transactions: or all or nothing... This this this, and again, this.

Re: [GENERAL] db server processes hanging around

On 06/19/2012 09:29 AM, Mark Rostron wrote: hi we are running out of database connections. we are using pg 9.0.6 on linux centos 5.7 64bit. we are not using any go-between connection pools such as pgbouncer or pgpool - connections occur directly from client to database. the connection setup on

Re: [GENERAL] bytea insert difference between 8.3 and 9.x

Merlin Moncure-2 wrote > > m_connection.prepare("INSERT INTO foo(x) VALUES($1)") ("bytea", > pqxx::prepare::treat_binary); > I have the same problem. I can't get accsess to pqxx::prepare::treat_binary. And m_connection.prepare(...) two arguments only. What i mist? -- View this message in cont

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

And I will be pleased that data is gone! I really did not expect anything but this. If I need such tolerant behavior, then this shall be a feature of my special app, not a feature of the database... If the developer does not know how to write sql, then is time to learn. If the problem is the dyn

[GENERAL] Special ORDER BY

How can I order a result set by a special order of a column. For example, let's say I have this query SELECT * FROM subcampo sc JOIN campo c ON (c.codigo = sc.campo) WHERE c.etiqueta IN (245, 130, 240, 243, 246, 490, 630, 730, 740) Now I want to order the result set using c.etiqueta but wi

Re: [GENERAL] Special ORDER BY

You can use something like "CASE WHEN" .. and use a number to order your data... like this SELECT *, CASE WHEN c.etiqueta = 245 THEN 1 WHEN c.etiqueta = 130 THEN 2 WHEN c.etiqueta = 240 THEN 3 WHEN c.etiqueta = 243 THEN 4 WHEN c.etiqueta = 246 THEN 5 WHEN c.eti

Re: [GENERAL] Special ORDER BY

Helo This is provided that the list is constant SELECT * FROM subcampo sc JOIN campo c ON (c.codigo = sc.campo) JOIN (values(245,1),(130,2), (240,3), (243,4), (246,5), (490,6), (630,7), (730,8), (740,9)) as d(dato,orden) on (c.etiqueta =d.dato) ORDRE BY d.orden On Tue, Jun 19, 2012 a

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

On Wed, 2012-06-20 at 08:27 +1200, Gavin Flower wrote: [] > > > > > > > > I would be be extremely concerned about any move to allow syntax > errors not to abort a transaction. Me too. But it's a nuicence for interractive session when transactions breakes due to syntax error -

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

But that data was supposed to get transferred into another table first! Data shouldn't just disappear like that. If you want that kind of behaviour use a different db that likes to throw your data away when it shouldn't. On Tue, Jun 19, 2012 at 1:09 PM, Edson Richter wrote: > And I will be pleas

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

I like the current behavior. Having been pleasantly surprised that this is how Pg operates, it is very helpful when I'm working on scripts or batches such as for creating or populating schemas. If it dies part way through, I know I can just fix the problem and rerun the whole thing, without ha

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

On 20/06/12 01:35, Rafal Pietrak wrote: On Tue, 2012-06-19 at 19:06 +0800, Craig Ringer wrote: On 06/19/2012 02:20 PM, Tom Lane wrote: So you're suggesting that "SELECT 1/0;" should terminate a transaction, but "SELECT 1//0;" should not? How about "ROLBACK;"? It gets pretty squishy pretty fa

Re: [GENERAL] pgstat wait timeout : permission denied

2012/6/19 Alban Hertroys : >> 2012-06-19 09:33:38 CESTWARNING:  01000: pgstat wait timeout >> 2012-06-19 09:33:38 CESTLOCATION:  backend_read_statsfile, >> .\src\backend\postmaster\pgstat.c:3807 >> 2012-06-19 09:33:41 CESTLOG:  42501: could not rename temporary statistics >> file "pg_stat_tmp/pgsta

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

According to documentation, "TRUNCATEis transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit." You will find this description at following page: http://www.postgresql.org/docs/9.0/static/sql-truncate

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

On Tue, Jun 19, 2012 at 6:19 PM, Edson Richter wrote: > According to documentation, > > "TRUNCATE is transaction-safe with respect to the data in the tables: the > truncation will be safely rolled back if the surrounding transaction does > not commit." > > You will find this description at followi

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

Em 19/06/2012 22:26, Scott Marlowe escreveu: On Tue, Jun 19, 2012 at 6:19 PM, Edson Richter wrote: According to documentation, "TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit." You w

Re: [GENERAL] How to get no. of commits/rollbacks by application on the database?

On 06/19/2012 09:08 PM, Raghavendra wrote: > How can we figure out no. of commit/rollback happend on the database > by application ? > If am a DBA, how to know exact application or user commits/rollbacks happened on the database without enabling logs. This sounds like one of those "what are yo

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

On 19 June 2012 22:07, Tom Lane wrote: > Rafal Pietrak writes: >> The point is, that SQL syntax errors are so obviusly different from >> execution errors, that noting this distinction should not raise any >> ambiguity. > > I beg to disagree.  Typos can manifest themselves as execution errors > ju

[GENERAL] Re: Is there a way to ask PostgreSQL for the name of the computer it's running on?

CREATE OR REPLACE FUNCTION get_hostname() RETURNS text AS $BODY$use Sys::Hostname; return hostname;$BODY$ LANGUAGE plperlu VOLATILE; -- View this message in context: http://postgresql.1045698.n5.nabble.com/Is-there-a-way-to-ask-PostgreSQL-for-the-name-of-the-computer-it-s-running-on-tp571253