[GENERAL] FW: syntax for Copy command via libpqxx

Hi, Can anyone tell me the proper syntax for using copy command using libpqxx .? the manual in pqxx.org doesnot provide the full tutorial .Can anybody tell me a proper link where I can find the full tutorial for libpqxx? Thanks in advance, Roshni

Re: [GENERAL] Fwd: How do I determine my data dir for a created database for pg_ctl?

Matthew Pettis wrote: I have a database I can psql into... How can I determine what its absolute path is so I can use pg_ctl on it to restart it? Because when I use pg_ctl, it tells me I have to provide it in the -D flag as an argument. show data_directory; klint. -- Klint Gore Database Ma

Re: [GENERAL] schema name in SQL statement.

-Original Message- From: johnf <[EMAIL PROTECTED]> To: pgsql-general@postgresql.org Subject: Re: [GENERAL] schema name in SQL statement. Date: Tue, 19 Aug 2008 22:25:14 -0700 On Tuesday 19 August 2008 10:06:55 pm Scott Marlowe wrote: > On Tue, Aug 19, 2008 at 10:53 PM, johnf <[EMAIL PROTEC

[GENERAL] Fwd: Restarting with pg_ctl, users, and passwords.

Hi, I just installed postgresql 8.3 on Ubuntu Heron with Postgis. I've worked with this install on XP before, but not on Linux. I'm having trouble telling if the postmaster is started. But, maybe more root to the problem, I cannot log onto the database via 'psql' when I supply what I think I se

[GENERAL] Fwd: How do I determine my data dir for a created database for pg_ctl?

Hi, I have a database I can psql into... How can I determine what its absolute path is so I can use pg_ctl on it to restart it? Because when I use pg_ctl, it tells me I have to provide it in the -D flag as an argument. Thanks, matt -- It is from the wellspring of our despair and the places that

Re: [GENERAL] schema name in SQL statement.

On Tuesday 19 August 2008 10:06:55 pm Scott Marlowe wrote: > On Tue, Aug 19, 2008 at 10:53 PM, johnf <[EMAIL PROTECTED]> wrote: > > On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote: > >> From: "Masis, Alexander (US SSA)" <[EMAIL PROTECTED]> > >> > >> > I have to explicitly specify the sche

Re: [GENERAL] can't get UPDATE ... RETURNING ... INTO ... to compile successfully

Dale Harris wrote: As per the original message: >I've got some code which postgres 8.3.3 won't accept. Postgres doesn't like the INTO clause on RETURNING INTO and I've tried following the documentation. > >UPDATE "EntityRelation" SET "Status" = inStatus, "Modified" = Session_TimeStamp(),

Re: [GENERAL] schema name in SQL statement.

On Tue, Aug 19, 2008 at 10:53 PM, johnf <[EMAIL PROTECTED]> wrote: > On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote: >> From: "Masis, Alexander (US SSA)" <[EMAIL PROTECTED]> >> >> > I have to explicitly specify the schema name to make SQL statement to >> > work. >> > Can I set the schema

Re: [GENERAL] can't get UPDATE ... RETURNING ... INTO ... to compile successfully

On Tue, Aug 19, 2008 at 9:51 PM, Dale Harris <[EMAIL PROTECTED]> wrote: > As per the original message: >>UPDATE "EntityRelation" SET "Status" = inStatus, "Modified" = >> Session_TimeStamp(), "ModifiedBy" = UserID() WHERE ("RelationID" = >> inRelationID) AND ("EntityID" = inEnityID) AND IsEqual(inR

Re: [GENERAL] schema name in SQL statement.

On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote: > -- Original message -- > From: "Masis, Alexander (US SSA)" <[EMAIL PROTECTED]> > > > I have to explicitly specify the schema name to make SQL statement to > > work. > > Can I set the schema before the quer

Re: [GENERAL] can't get UPDATE ... RETURNING ... INTO ... to compile successfully

As per the original message: >I've got some code which postgres 8.3.3 won't accept. Postgres doesn't like the INTO clause on RETURNING INTO and I've tried following the documentation. > >UPDATE "EntityRelation" SET "Status" = inStatus, "Modified" = Session_TimeStamp(), "ModifiedBy" = UserID

Re: [GENERAL] can't get UPDATE ... RETURNING ... INTO ... to compile successfully

Dale Harris wrote: It works for the INSERT command, but not UPDATE. For the INSERT command, it makes my code look neater and I image it's more efficient too. This time I am trying to UPDATE a field using a primary key, and return another field into a variable so that I can take necessary action

Re: [GENERAL] 8.3.3 win32 crashing

Tom Lane wrote: Klint Gore <[EMAIL PROTECTED]> writes: >>> [ heavy RAISE NOTICE traffic crashes the server ] > client=warning, log=warning completes > client=warning, log=notice fails > client=notice, log=warning completes > client=notice, log=notice fails > It fails a heck of a lot quicker wit

Re: [GENERAL] can't get UPDATE ... RETURNING ... INTO ... to compile successfully

"Dale Harris" <[EMAIL PROTECTED]> writes: > In the following documentation it advises that the UPDATE statement should > be able to return a value into a variable in plpgsql. > http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html > It works for the INSERT command, but not UPDATE. As w

Re: [GENERAL] 8.3.3 win32 crashing

Klint Gore <[EMAIL PROTECTED]> writes: >>> [ heavy RAISE NOTICE traffic crashes the server ] > Tom Lane wrote: >> Please try to narrow it down a little. > client=warning, log=warning completes > client=warning, log=notice fails > client=notice, log=warning completes > client=notice, log=notice fa

Re: [GENERAL] 8.3.3 win32 crashing

Tom Lane wrote: Klint Gore <[EMAIL PROTECTED]> writes: > Faulting application postgres.exe, version 8.3.3.8160, faulting module > msvcr80.dll, version 8.0.50727.1433, fault address 0x0001e44a. > I have a function that's doing a summary report counting data. It makes > a couple of small temp

Re: [GENERAL] can't get UPDATE ... RETURNING ... INTO ... to compile successfully

Hi All, In the following documentation it advises that the UPDATE statement should be able to return a value into a variable in plpgsql. http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html It works for the INSERT command, but not UPDATE. For the INSERT command, it makes my code lo

Re: [GENERAL] schema name in SQL statement.

-- Original message -- From: "Masis, Alexander (US SSA)" <[EMAIL PROTECTED]> > I have to explicitly specify the schema name to make SQL statement to > work. > Can I set the schema before the query, or set a default schema? > My current statement: > "SELECT col FROM

[GENERAL] schema name in SQL statement.

I have to explicitly specify the schema name to make SQL statement to work. Can I set the schema before the query, or set a default schema? My current statement: "SELECT col FROM schema.table" I like to be able to use generic SQL statement like: "SELECT col FROM table" -- Sent via pgsql-general

Re: [GENERAL] WAL archiving to network drive

Rob Adams wrote: I'm setting up WAL archiving on a Windows machine & need to copy the WAL files to a network drive. Is it best to give the 'postgres' user network access & archive the WAL files directly to the network drive? Or archive the WAL files to a local folder and then use a scheduled

[GENERAL] WAL archiving to network drive

I'm setting up WAL archiving on a Windows machine & need to copy the WAL files to a network drive. Is it best to give the 'postgres' user network access & archive the WAL files directly to the network drive? Or archive the WAL files to a local folder and then use a scheduled task to move them

Re: [GENERAL] PostgreSQL on Windows x64

On 8/19/08, Goboxe <[EMAIL PROTECTED]> wrote: > Dave, > > Thanks for the link. np. > Our app now running using EnterpriseDB 8.2 on x86. > Plan to test them on x64 server once we got it. > > Do you know if jdbc, npgsql, PostgreSQL ANSI odbc drivers bundled in > EnterpriseDB can run without any iss

Re: [GENERAL] Pg dump Error

tuanhoanganh escribió: > I have a problem with pg_dump on 2 computers. On the first computer, pg_dump > runs very well, but on the second pg_dump has a error : > pg_dump: reading triggers for table "ph57" > pg_dump: reading triggers for table "phts" > pg_dump: reading dependency data > pg_dump: SQL

Re: [GENERAL] DELETE

Add the USING list clause: usinglist A list of table expressions, allowing columns from other tables to appear in the WHERE condition. This is similar to the list of tables that can be specified in the FROM Clause of a SELE

[GENERAL] DELETE

hello, what will be the error in syntax of following statement? delete from accountingtransactions where accountingtransactions.refaccdocid=docs.docid and docs.tmpselect=-1 and docs.tmpselectedby= $1; when a new function is created it does not gives any error but when this function is called from

[GENERAL] cache lookup failed

I got following error while testing some newly created functions. ERROR: cache lookup failed for function 111462 CONTEXT: PL/pgSQL function "uf_postdoc" line 25 at FOR over SELECT rows ** Error ** ERROR: cache lookup failed for function 111462 SQL state: XX000 Context: PL/pgSQ

Re: [GENERAL] What's size of your PostgreSQL Database?

On Sat, 2008-08-16 at 11:42 +0800, Amber wrote: > Dear all: > We are currently considering using PostgreSQL to host a read only > warehouse, we would like to get some experiences, best practices and > performance metrics from the user community, following is the question list: > 1. What's s

Re: [GENERAL] What's size of your PostgreSQL Database?

On Tue, 2008-08-19 at 07:34 -0400, Bill Moran wrote: > > In theory, you can have so many disks that the bottleneck moves to > some > other location, such as the IO bus or memory or the CPU, but I've > never > heard of that happening to anyone. Also, you want to get fast, high- > quality disks, a

Re: [GENERAL] Updates and deletes with joins

On Tuesday 19 August 2008, Gordon <[EMAIL PROTECTED]> wrote: > I want to be able to restrict any query that updates or deletes from > the articles table so that they can only occur if there isn't a > corresponding entry in the locks table. As far as I can tell, > however, you can't join tables whe

Re: [GENERAL] PostgreSQL on Windows x64

Dave, Thanks for the link. Our app now running using EnterpriseDB 8.2 on x86. Plan to test them on x64 server once we got it. Do you know if jdbc, npgsql, PostgreSQL ANSI odbc drivers bundled in EnterpriseDB can run without any issues on x64? Thanks, Amin On Aug 19, 3:50 pm, [EMAIL PROTECTED]

[GENERAL] Updates and deletes with joins

I'm working on a CMS, one of the features of the CMS in question is that only one user can edit an item at any given time. I've implemented this by having one table that holds the items, and another table that holds locks. A lock row consists of the ID of the item locked, a timestamp indicating w

Re: [GENERAL] Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully

Alvaro Herrera <[EMAIL PROTECTED]> writes: > Bill Moran wrote: >> Is it possible that your UPDATE command is updating multiple rows? I >> don't believe RETURNING will work on an UPDATE that touches more than 1 >> row. > Hmm, why not? plpgsql will reject UPDATE RETURNING INTO that returns more th

Re: [GENERAL] Idle in transcation problem?

"Charles.Hou" <[EMAIL PROTECTED]> writes: > i use the "ps -ef | grep postgres" to see all the connections. the > connection's status is "Idle in transcation". > and i use the " du -h /mydb" to check the disk size. The disk size of > mydb has increased from 400MB to 600MB. > Why? because of the "i

Re: [GENERAL] Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully

In response to Alvaro Herrera <[EMAIL PROTECTED]>: > Bill Moran wrote: > > > Is it possible that your UPDATE command is updating multiple rows? I > > don't believe RETURNING will work on an UPDATE that touches more than 1 > > row. > > Hmm, why not? Because a doc linked to earlier in this threa

Re: [GENERAL] Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully

Bill Moran wrote: > Is it possible that your UPDATE command is updating multiple rows? I > don't believe RETURNING will work on an UPDATE that touches more than 1 > row. Hmm, why not? alvherre=> create table bill (a int, b text); CREATE TABLE alvherre=> insert into bill values (1, 'one'); INSER

Re: [GENERAL] How to execute 'set session role' from plpgsql function?

"Albe Laurenz" <[EMAIL PROTECTED]> writes: >EXECUTE 'SET SESSION ROLE "' || lower(regexp_replace(wishedrole, '"', '', > 'g')) || '"'; > The "lower" and "regexp_replace" are there to prevent SQL injection. quote_ident() would be a far better solution. regards, tom lan

Re: [GENERAL] 8.3.3 win32 crashing

Klint Gore <[EMAIL PROTECTED]> writes: > Faulting application postgres.exe, version 8.3.3.8160, faulting module > msvcr80.dll, version 8.0.50727.1433, fault address 0x0001e44a. > I have a function that's doing a summary report counting data. It makes > a couple of small temp tables then makes a

Re: [GENERAL] default postgresql.conf

Steve Clark a écrit : > Hello List, > > When an initdb is done where do the default postgresql.conf and > pg_hba.conf come from? Are they > copied from some template file? > They are copied from the share subdirectory. For example, in Debian, it's in: /usr/share/postgresql//postgresql.conf.sam

[GENERAL] default postgresql.conf

Hello List, When an initdb is done where do the default postgresql.conf and pg_hba.conf come from? Are they copied from some template file? Thanks, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailp

[GENERAL] Idle in transcation problem?

i use the "ps -ef | grep postgres" to see all the connections. the connection's status is "Idle in transcation". and i use the " du -h /mydb" to check the disk size. The disk size of mydb has increased from 400MB to 600MB. Why? because of the "idle in transcation" ? my pg version:8.1.3 OS: Lin

Re: [GENERAL] NOT DEFERRABLE as default, why and how to manage it.

Am Tuesday, 19. August 2008 schrieb Ivan Sergio Borgonovo: > > Is there any reason they put it that way in the standard other than > > the mantra "stricter is better"? > > After reflecting a bit I think it is a matter of "failing earlier". Deferrable constraints are an optional feature of SQL, and

Re: [GENERAL] CASE

c k wrote: Hi, I want to create a function as follows: case variable=value1 SQL statements case variable=value2 SQL statements case else end of case Looks like you want to use pl/pgsl and IF ... THEN ... ELSE instead? Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature

Re: [GENERAL] What's size of your PostgreSQL Database?

On Tue, 2008-08-19 at 02:28 -0400, David Wilson wrote: > On Fri, Aug 15, 2008 at 11:42 PM, Amber <[EMAIL PROTECTED]> wrote: > > Dear all: > >We are currently considering using PostgreSQL to host a read only > > warehouse, > we would like to get some experiences, best practices and performance

Re: [GENERAL] CASE

Hi, I want to create a function as follows: case variable=value1 SQL statements case variable=value2 SQL statements case else end of case CPK On Tue, Aug 19, 2008 at 5:17 PM, Bill Moran <[EMAIL PROTECTED]>wrote: > In response to "c k" <[EMAIL PROTECTED]>: > > > Is there any control structu

Re: [GENERAL] CASE

In response to "c k" <[EMAIL PROTECTED]>: > Is there any control structure statement similar to select case ... ? If yes > how to use it. I have tried to use, case when then > end, but not worked well as I want to use other control structures and sql > statements to be executed for each case. P

Re: [GENERAL] Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully

In response to Dale <[EMAIL PROTECTED]>: > On Aug 18, 9:23 pm, Lew <[EMAIL PROTECTED]> wrote: > > Dale wrote: > > > Hi, > > > I've got some code which postgres 8.3.3 won't accept.  Postgres > > > doesn't like the INTO clause on RETURNING INTO and I've tried > > > following the documentation. > > >

Re: [GENERAL] What's size of your PostgreSQL Database?

In response to Ow Mun Heng <[EMAIL PROTECTED]>: > On Mon, 2008-08-18 at 11:01 -0400, justin wrote: > > Ow Mun Heng wrote: > > > -Original Message- > > > From: Scott Marlowe <[EMAIL PROTECTED]> > > > > > > > If you're looking at read only / read > > > > mostly, then RAID5 or 6 might be

Re: [GENERAL] CASE

Hello Develop PL/pgSQL (PostgreSQL 8.4) has CASE statement. Actual and older version hasn't nothing similar. Regards Pavel Stehule 2008/8/19 c k <[EMAIL PROTECTED]>: > Is there any control structure statement similar to select case ... ? If yes > how to use it. I have tried to use, case when t

[GENERAL] CASE

Is there any control structure statement similar to select case ... ? If yes how to use it. I have tried to use, case when then end, but not worked well as I want to use other control structures and sql statements to be executed for each case. Please reply. Thanks CPK.

Re: [GENERAL] NOT DEFERRABLE as default, why and how to manage it.

On Tue, 19 Aug 2008 10:49:11 +0200 Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > On Tue, 19 Aug 2008 11:20:08 +0300 > Peter Eisentraut <[EMAIL PROTECTED]> wrote: > > Am Tuesday, 19. August 2008 schrieb Ivan Sergio Borgonovo: > > > I just learnt that NOT DEFERRABLE is default. > > > Is it ma

Re: [GENERAL] How to execute 'set session role' from plpgsql function?

Олег Василенко wrote: > I wish to have a function with code above, but compiller generate > syntactic error at the line "SET SESSION ROLE wishedrole;". > > How to pass the wishedrole value to the structure? > > CREATE OR REPLACE FUNCTION f_switch_role(INOUT text,INOUT boolean) AS > $BODY$ > DE

[GENERAL] 8.3.3 win32 crashing

Does this mean anything to anyone? Faulting application postgres.exe, version 8.3.3.8160, faulting module msvcr80.dll, version 8.0.50727.1433, fault address 0x0001e44a. I have a function that's doing a summary report counting data. It makes a couple of small temp tables then makes a big temp

Re: [GENERAL] NOT DEFERRABLE as default, why and how to manage it.

On Tue, 19 Aug 2008 11:20:08 +0300 Peter Eisentraut <[EMAIL PROTECTED]> wrote: > Am Tuesday, 19. August 2008 schrieb Ivan Sergio Borgonovo: > > I just learnt that NOT DEFERRABLE is default. > > > Is it mandated by SQL standard? > > Yes. Is there any reason they put it that way in the standard o

Re: [GENERAL] NOT DEFERRABLE as default, why and how to manage it.

Am Tuesday, 19. August 2008 schrieb Ivan Sergio Borgonovo: > I just learnt that NOT DEFERRABLE is default. > Is it mandated by SQL standard? Yes. > Is there any shortcut if I've to change to deferrable most of my > constraints? Probably not, short of writing a little script. > Other than pgfou

[GENERAL] Pg dump Error

I have a problem with pg_dump on 2 computers. On the first computer, pg_dump runs very well, but on the second pg_dump has a error : pg_dump: reading triggers for table "ph57" pg_dump: reading triggers for table "phts" pg_dump: reading dependency data pg_dump: SQL command failed pg_dump: Error mess

[GENERAL] NOT DEFERRABLE as default, why and how to manage it.

I just learnt that NOT DEFERRABLE is default. I vaguely understand that generally stricter policies protect distracted programmers from making mistakes... but missing an "alter constraint" it makes refactoring a PITA. Is it mandated by SQL standard? Any other rational reason to make NOT DEFERRABLE

Re: [GENERAL] PostgreSQL on Windows x64

On Mon, Aug 18, 2008 at 7:48 AM, Goboxe <[EMAIL PROTECTED]> wrote: > Hi, > > Does PostgreSQL can run on Windows x64? > If yes, which version? All versions (though you should start with 8.3.3): http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows#Is_there_a_64-bit_bui