Re: [GENERAL] Trigger Performance

Before reading. This is solved. Was an error on my part. On Sun, 2011-01-16 at 03:46 +, Jasen Betts wrote: > In plpgsql IF is an implicit select. > > > IF EXISTS (SELECT 1 FROM t1 WHERE > volume_id

[GENERAL] Trigger Performance

Hi, I've created a trigger that checks the uniqueness of two columns in a table. Traditionally, one would use a unique constraint, but in my case, the size of the unique index would be too large and some performance loss is acceptable. However, the trigger performance seems to be far below what'

Re: [GENERAL] Sequence reset

I can't tell what a roll is based on your question, but here is how to set a sequence to a specific value. SELECT setval('foo', 42); Next nextval will return 43 http://www.postgresql.org/docs/8.4/interactive/functions-sequence.html -Randall On Mon, 2010-08-23 at 05:04 -0500, SUNDAY A.

Re: [GENERAL] append_array causes rapid growth according to pg_total_relation_size

More information including a workaround. VACUUM ANALYZE is not freeing space after an array_append, but it does after replacing a specific element in the array. So if I do this: update array_big1 set chunk_ids = array_append(chunk_ids, '1') where id = 0; update array_big1 set chunk_ids[1] = chun

[GENERAL] append_array causes rapid growth according to pg_total_relation_size

Hi, Using Postgres 8.4, I create this table: rcs_test=> \d array_big1; Table "public.array_big1" Column | Type | Modifiers ---+--+--- id| integer | not null chunk_ids | bigint[] | Indexes: "array_big1_pkey" PRIMARY KEY, btree (id) It's initial

Re: [GENERAL] pre-parser query manipulation

Erik Jones wrote: This may be a dupe. I sent and had some issues with my mail client and afterwards it was sitting all alone in my outbox, if you've already seen this, sorry for the resend... On Mar 14, 2007, at 2:08 PM, Randall Smith wrote: Hi, I'm attempting to process a que

Re: [GENERAL] pre-parser query manipulation

Erik Jones wrote: This may be a dupe. I sent and had some issues with my mail client and afterwards it was sitting all alone in my outbox, if you've already seen this, sorry for the resend... On Mar 14, 2007, at 2:08 PM, Randall Smith wrote: Hi, I'm attempting to process a que

Re: [GENERAL] [Bulk] Re: quoted identifier behaviour

Stephan Szabo wrote: On Wed, 14 Mar 2007, Randall Smith wrote: Scott Marlowe wrote: This whole discussion is reminding me of one of my personal mantras, and that is that relying on "artifacts" of behaviour is generally a bad idea. For instance, many databases accept != for not equa

Re: [GENERAL] [Bulk] Re: quoted identifier behaviour

Alvaro Herrera wrote: Randall Smith wrote: Scott Marlowe wrote: This whole discussion is reminding me of one of my personal mantras, and that is that relying on "artifacts" of behaviour is generally a bad idea. For instance, many databases accept != for not equal, but the sql stan

Re: [GENERAL] [Bulk] Re: quoted identifier behaviour

Scott Marlowe wrote: This whole discussion is reminding me of one of my personal mantras, and that is that relying on "artifacts" of behaviour is generally a bad idea. For instance, many databases accept != for not equal, but the sql standard quite clearly says it's <>. If you're relying on cas

Re: [GENERAL] [Bulk] Re: quoted identifier behaviour

Ted Byers wrote: - Original Message - From: "Randall Smith" <[EMAIL PROTECTED]> I'll give the pghackers forum a visit and since I'm already on the subject here, I'll make a direct comparison of the situation. Microsoft's Internet Explorer web br

Re: [GENERAL] quoted identifier behaviour

Thomas Kellerer wrote: Randall Smith wrote on 14.03.2007 18:59: I'm trying to get Postgresql to work with software that uses JDBC and Oracle for a large government project. So I have to report that the application won't work with Postgresql because it (PG) doesn't adhere

Re: [GENERAL] quoted identifier behaviour

This is the last statement I found on the issue, which is someone hopeful. http://archives.postgresql.org/pgsql-hackers/2006-11/msg00347.php Randall ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to

Re: [GENERAL] quoted identifier behaviour

Tom Lane wrote: Randall Smith <[EMAIL PROTECTED]> writes: I'm an advocate of Postgresql and usually tout SQL compliance as a strength, so it bothers me that this is not in line to be corrected. It's not that it's not on the radar screen, it's just that no one sees a

[GENERAL] pre-parser query manipulation

Hi, I'm attempting to process a query written in the form: SELECT fields FROM table WHERE conditions FOR UPDATE OF field_name when Postgresql supports: SELECT fields FROM table WHERE conditions FOR UPDATE OF table_name I'd like to remove the OF clause in the case only one table is que

Re: [GENERAL] quoted identifier behaviour

Albe Laurenz wrote: Randall Smith wrote: Are there plans to make Postgresql's behavior SQL compliant with regards to quoted identifiers? My specific need is to access tables named in lower case by an uppercase quoted identifier, which is in line with the SQL standard. So if I cr

[GENERAL] quoted identifier behaviour

Are there plans to make Postgresql's behavior SQL compliant with regards to quoted identifiers? My specific need is to access tables named in lower case by an uppercase quoted identifier, which is in line with the SQL standard. So if I created table foo, I should be able to access it like "FO

[GENERAL] connecting to server process via sockets

For fun and learning, I would like to connect to the Postgresql backend and issue queries using sockets. I'm using Python's socket module. I'm new to socket programming, but I'm experienced with Python and Postgresql. I've been using the JDBC driver and the online documentation as a guide, b

Re: [GENERAL] oracle synchronization strategy

For anyone interested, the below procedure worked well. Randall Randall Smith wrote: I am going to sync a schema in postgres with one in an oracle db. The tables are simple, but there are 200 of them. I would like to try to keep the sync lag < 1 minute. Here is my idea. Please criti

Re: [GENERAL] oracle synchronization strategy

Thanks Joachim, The mirror only has to go from oracle to pgsql and the schema/tables never change. I'm going to take a look at dbmirror. Thanks for the advice. Randall Joachim Wieland wrote: Hi Randall, On Sun, Oct 31, 2004 at 11:25:46PM -0600, Randall Smith wrote: 1. Set up stored pr

[GENERAL] oracle synchronization strategy

I am going to sync a schema in postgres with one in an oracle db. The tables are simple, but there are 200 of them. I would like to try to keep the sync lag < 1 minute. Here is my idea. Please critique/suggest. 1. Set up stored proc on oracle that records a INSERT, UPDATE, DELETE SQL action

[GENERAL] initdb on Debian

I would like to be able to initdb to get a clean database to test backups. Reading the Debian version of the Postgresql README, I see that I should use the --debian-conffile option with initdb. Problem though. I have to run initdb as user postgres, but I need to be root to edit the stuff in

[GENERAL] zero-length delimited identifier in pg_dumpall file

Got this error on a pg_dumpall file dumped from a postgresql-7.4-0.3PGDG database and imported into 7.4.5-3 database. ERROR: zero-length delimited identifier at or near at character 37 From looking at the output, I think maybe this is the line that caused the error. CREATE SCHEMA "MSV80"

Re: [GENERAL] cross-table unique constraint

Wolff III wrote: On Tue, Jan 06, 2004 at 07:10:28 +, Randall Smith <[EMAIL PROTECTED]> wrote: Is it possible to create a unique constraint on multiple fields in related tables? Not with the normal UNIQUE constraint. That will do multiple columns in one table, but won't span tabl

Re: [GENERAL] PostgreSQL on Cobalt Raq and Cold Fusion on NT using ODBC?

Thanks for everyone who helped. I'm making progress. A quick port scan revealed that postmaster was running on 5583. I guess Cobalt in non-standard. I can now talk to postmaster but I'm getting an authenication error like: ODBC Error Code = S1000 (General error) The database does not exist on the

Re: [GENERAL] PostgreSQL on Cobalt Raq and Cold Fusion on NT using ODBC?

n I verify postmaster is accepting tcp connections? Randy Smith Tiger Mountain Technologies - Original Message - From: Tom Lane <[EMAIL PROTECTED]> To: Randall Smith <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, September 25, 2000 1:06 PM Subject: Re: [GENER