Re: [GENERAL] PostgreSQL function can not load dll library.

[Craig Ringer]

On Tue, 2009-10-27 at 02:19 -0400, a.bhattacha...@sungard.com wrote:

> I am creating a dll using MSVC 2005 and trying to call the dll from my
> Postgres function

OK, so you're creating a PostgreSQL module implementing one or more
SQL-callable functions in C. When you load it into the server with
`CREATE FUNCTION' ?

>  but unfortunately PostgreSQL is throwing an error message saying:
> 
>  
> 
> ERROR: could not load library "C:/Program
> Files/PostgreSQL/8.3/lib/watchlist.dll": The specified module could
> not be found.

Assuming that the file is, in fact, on the path you've specified: Is it
possible that your DLL links to other DLLs that are not on the
PostgreSQL server's path? If your DLL links to another that cannot be
found, that error message is the one you'll get.

Dependency walker (depends.exe) from http://dependencywalker.com/ may
help you track that down.

--
Craig Ringer



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PostgreSQL function can not load dll library.

[A.Bhattacharya]

Hi All,

 

I am creating a dll using MSVC 2005 and trying to call the dll from my
Postgres function but unfortunately PostgreSQL is throwing an error
message saying:

 

ERROR: could not load library "C:/Program
Files/PostgreSQL/8.3/lib/watchlist.dll": The specified module could not
be found.

SQL state: 58P01

 

However I have created the dll and exported the required function to
make the dll the callable by PostgeSQL.

I am using PostgreSQL 8.3 and the dll have been created successfully
without any compilation errors.

 

It would be nice if anyone can help me as I am stuck in this for almost
a week.

 

Many thanks.

 

AB

 

 

 

Re: [GENERAL] Is there any ways to pass an array as parameter in libpq?

[Craig Ringer]

On Tue, 2009-10-27 at 08:07 +0800, ChenXun wrote:

> Hello,
> 
> I'm starting to learn programming with libpq.
> In the main loop of my code, I will receive some data in the format of
> an array of a struct. The data will be inserted to the database, in
> different lines. I also need to update the last record in the table
> before the insertion.


You appear to be thinking of a table as an ordered list of data. It
doesn't work like that. There is no "last record" in the table, and the
records aren't in any particular order.

If you want to get them out of the database in a particular order you
must specify that order with an ORDER BY clause in your SELECT
statements. Otherwise they'll be returned in whatever order is quickest
for the database - which will probably initially be the order you
inserted them in, but that'll change over time.

I suspect you may be trying to do things in a way that's going to make
things MUCH harder for you down the track.

You should just be able to do a parameterized INSERT INTO where you loop
over the elements of the array in your code, feeding them in as query
parameters. If you have too much data for that you could do a
multi-record INSERT (say insert ten records at a time). If that still
isn't good enough, then the network COPY protocol may be what you need.
I really doubt, though, that you need to do anything more than loop over
the array in your program and INSERT from it one-by-one within a
transaction.

--
Craig Ringer

Re: [GENERAL] Implementing Frontend/Backend Protocol TCP/IP

[Craig Ringer]

On Mon, 2009-10-26 at 20:15 -0300, Alvaro Herrera wrote:

> Raimon Fernandez wrote:
> > 
> > 
> > Hello,
> > 
> > 
> > I'm trying to implement the front-end protocol with TCP from
> > REALbasic to PostgreSQL.
> 
> That sounds the most difficult way to do it.  Can't you just embed
> libpq?


+1

Almost all languages support some kind of C bindings or provide a
dlopen-like mechanism to dynamically call C functions from shared
libraries. 

RealBasic appears to have fairly dynamic, dlopen-style bindings. I'm
sure you can find more information in the manual, but here's an example
of some syntax:

http://forums.realsoftware.com/viewtopic.php?t=5050

You'll have to do a bit more work to produce bindings for libpq, though,
especially if you have to produce bindings for any data types (C
structures). If all you have to bind is function calls, and you can
handle any libpq-specific structures as opaque void pointers then it
shouldn't be too hard to just bind the function calls you need.

--
Craig Ringer

[GENERAL] How does PostgreSQL recognise "deleted" tuples by using xmax ?

[纪晓曦]

How does PostgreSQL recognise "deleted" tuples by using xmax ?

Re: [GENERAL] cursor MOVE vs OFFSET in SELECT

[silly8888]

On Mon, Oct 26, 2009 at 10:28 AM, Alvaro Herrera
 wrote:
> silly escribió:
>> 2009/10/26 Grzegorz Jaśkiewicz :
>> >
>> >
>> > On Mon, Oct 26, 2009 at 10:30 AM, silly  wrote:
>> >>
>> >> Suppose that you have a query, say $sql_query, which is very
>> >> complicated and produces many rows. Which of the following is going to
>> >> be faser:
>> >>
>> >>    $sql_query OFFSET 3000 LIMIT 12;
>> >>
>> >> or
>> >>
>> >>    BEGIN;
>> >>    DECLARE cur1 CURSOR FOR $sql_query;
>> >>    MOVE 3000 IN cur1;
>> >>    FETCH 12 FROM cur1;
>> >>    COMMIT;
>> >>
>> >> Naturally, the former cannot be slower than the latter. So my question
>> >> essentially is whether the MOVE operation on a cursor is
>> >> (significantly) slower that a OFFSET on the SELECT.
>> >
>> >
>> > OFFSET/LIMIT. Afaik cursor always fetches everything.
>>
>> Well, in my experiments they always perform the same. I suspect that
>> the way SELECT/OFFSET is implemented is not much different than
>> cursor/MOVE.
>
> The cursor could choose a different plan due to the "fast startup"
> behavior that Pavel alludes to.  You can actually change that by setting
> the cursor_tuple_fraction parameter.  Whether this plan is faster or
> slower than the other one is problem dependent.
>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>


OK, so based on what Alvaro & Pavel said, the following two
possibilities are equivalent as far as the query planner is concerned:

   $sql_query OFFSET 3000 LIMIT 12;

or

   BEGIN;
   SET LOCAL cursor_tuple_fraction=1;
   DECLARE cur1 CURSOR FOR $sql_query;
   MOVE 3000 IN cur1;
   FETCH 12 FROM cur1;
   COMMIT;

The problem is that in the latter case, the query planner doesn't know
in advance that we are going to skip the first 3000 rows.

-- 
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] drop view and recreate - for sync

[Craig Ringer]

Vick Khera wrote:
> On Fri, Oct 23, 2009 at 6:44 PM, Sydney Puente  wrote:
>> Ah I see what you mean - thanks very much that is v helpful!
>> Yes the copy will be read-only.
>> Will have 3 tables of data, being read (readonly) and in the background
>> Will have 3 shadow tables populated from an unreliable db over an unreliable
>> network.
>> not quite sure how I can "insert all the rows" in sql.
>> have postgres 8.03 BTW.
>>
> 
> If your network is unreliable, then perhaps the thing to do is have
> your program first fetch all the new data over that network into tab
> delimited files, one per table.  Then to insert all your data, just
> use the "COPY" command in postgres to read it all as one hunk of data.

Doing things this way will also let you avoid having the shadow tables.
You can copy the data to the server as a simple file using an
error-tolerant tool that can resume uploads. Ftp with ssl is one option
 - but please don't use plain ol' insecure FTP.

Once the csv file has made it to the Pg server host, by using the 'COPY'
command you can load it into the target tables as part of one
transaction. Your apps will see the update as atomic.

Assuming your data files are comma-separated:

BEGIN;
TRUNCATE TABLE data1, data2, data3;
COPY data1 FROM '/path/to/data1.csv' WITH CSV;
COPY data2 FROM '/path/to/data2.csv' WITH CSV;
COPY data3 FROM '/path/to/data3.csv' WITH CSV;
COMMIT;   -- At this moment your other apps suddenly see the changes

--
Craig Ringer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Is there any ways to pass an array as parameter in libpq?

[ChenXun]

Hello,

I'm starting to learn programming with libpq.
In the main loop of my code, I will receive some data in the format of an array 
of a struct. The data will be inserted to the database, in different lines.
I also need to update the last record in the table before the insertion. So I 
plan to write a pl/pgsql function (procedure) to perform the whole updating and 
inserting.
But I don't know how to pass the array to the procedure through libpq. It seems 
the only way is to using libpq to do updating and inserting separately. Like 
this

for(;;) {
/* receive data */

/* libpq updating */
  PQexec(...);

  PQprepare(...);
  for (i=0; ihttp://10.msn.com.cn

Re: [GENERAL] Implementing Frontend/Backend Protocol TCP/IP

[John R Pierce]

Alvaro Herrera wrote:

I'm trying to implement the front-end protocol with TCP from
REALbasic to PostgreSQL.



That sounds the most difficult way to do it.  Can't you just embed
libpq?
  


yah, seriously.   the binary protocol is not considered stable, it can 
change in subtle ways in each version.  libpq handles the current 
version and all previous versions, and exposes all methods.




--
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] Implementing Frontend/Backend Protocol TCP/IP

[Alvaro Herrera]

Raimon Fernandez wrote:
> 
> 
> Hello,
> 
> 
> I'm trying to implement the front-end protocol with TCP from
> REALbasic to PostgreSQL.

That sounds the most difficult way to do it.  Can't you just embed
libpq?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
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 list user-specific configuration parameters?

[Tim Landscheidt]

Alvaro Herrera  wrote:

>> how can I list the user-specific configuration parameters,
>> i. e. those set by "ALTER ROLE name SET ..."?

> Get them from the pg_authid catalog.

> 8.5 alpha2 has a new \drds command in psql for that purpose.

Thanks!

Tim


-- 
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] CSV files & empty strings

[Niklas Johansson]

On 20 okt 2009, at 16.15, Raymond O'Donnell wrote:


On 20/10/2009 05:55, Nate Randall wrote:

However, I need some method of "converting" the
empty string "" values into NULL values after import, so that I can
change the date fields back to date-type.  Any suggestions on how  
this

could be accomplished?


How about:

  update  set  = null where  = '';



You can also do the update and the column data type change in one  
fell swoop like so:


ALTER TABLE table_name ALTER COLUMN column_name
SET DATA TYPE DATE USING NULLIF(column_name, '')::DATE;




Sincerely,

Niklas Johansson




--
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] [ANNOUNCE] [pgeu-general] PGDay.EU 2009 - approaching fast!

[Joshua D. Drake]

On Mon, 2009-10-26 at 21:36 +, Peter Geoghegan wrote:
> > Sure ? You *have to* ask for the ParisTech rooms (there were still 10 rooms
> > available on friday, only for Paristech) Else the hotel is full.
> 
> I called on Saturday afternoon. I was sure to specify that I was a
> Paristech member, but they were still apparently fully booked.

I am staying here:

http://www.elysees-paris-hotel.com/

It is a little farther out, but puts you right in Paris.


> 
> Regards,
> Peter Geoghegan
> 


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - 
Salamander


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Implementing Frontend/Backend Protocol TCP/IP

[Raimon Fernandez]

Hello,


I'm trying to implement the front-end protocol with TCP from REALbasic  
to PostgreSQL.


The docs from PostgreSQL, well, I understand almost, but there are  
some points that maybe need more clarification.


Anyone have some experience to start making questions ?

:-)


The front-end tool is REALbasic but can be any tool that have TCP/IP  
comunication, so here is irrelevant.


Actually I can connect to Postgre Server, get and parse some  
parameters, and send some SELECT, but I don't like how I'm doing, so  
any guidence or wiki or blog or how-to where I can get more  
information, it would be perfect...


thanks for your time,

regards,

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] [ANNOUNCE] [pgeu-general] PGDay.EU 2009 - approaching fast!

[Peter Geoghegan]

> Sure ? You *have to* ask for the ParisTech rooms (there were still 10 rooms
> available on friday, only for Paristech) Else the hotel is full.

I called on Saturday afternoon. I was sure to specify that I was a
Paristech member, but they were still apparently fully booked.

Regards,
Peter Geoghegan

-- 
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 do I pipe output from query to a file in PostgreSQL?

[Raymond O'Donnell]

On 26/10/2009 18:03, Penrod, John wrote:
> In oracle:
> 
> SPOOL filename.txt
> Select * from customer;
> SPOOL OFF
> 
> Results are piped to filename.txt
> 
> 
> How do I do this from the psql command line?

\g  will execute the query and send the result to .


Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
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 do I pipe output from query to a file in PostgreSQL?

[Kevin Kempter]

On Monday 26 October 2009 12:03:13 Penrod, John wrote:
> In oracle:
> 
> SPOOL filename.txt
> Select * from customer;
> SPOOL OFF
> 
> Results are piped to filename.txt
> 
> 
> How do I do this from the psql command line?
> 
> 
> John J. Penrod, OCP
> Oracle/EnterpriseDB Database Administrator
> St. Jude Children's Research
>  Hospital 10VgnVCM100e2015acRCRD&plt=STJGENSEGOOGL009&gclid=CM6Imp6I0Z0CFSMND
> QodNXLerQ> 262 Danny Thomas Place, MS 0574
> Memphis, TN  38105
> Phone: (901) 595-4941
> FAX: (901) 595-2963
> john.pen...@stjude.org
> 
> 
> 
> 
>   
> Email Disclaimer: www.stjude.org/emaildisclaimer
> 


psql
postgres=# \o file.out
postgres=# select * from customer;

-- 
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 do I pipe output from query to a file in PostgreSQL?

[Penrod, John]

In oracle:

SPOOL filename.txt
Select * from customer;
SPOOL OFF

Results are piped to filename.txt


How do I do this from the psql command line?


John J. Penrod, OCP
Oracle/EnterpriseDB Database Administrator
St. Jude Children's Research 
Hospital
262 Danny Thomas Place, MS 0574
Memphis, TN  38105
Phone: (901) 595-4941
FAX: (901) 595-2963
john.pen...@stjude.org




  
Email Disclaimer: www.stjude.org/emaildisclaimer

Re: [GENERAL] Postmaster taking 100% of the CPU

[David Kerr]

On Mon, Oct 26, 2009 at 04:38:51PM -0400, Tom Lane wrote:
- David Kerr  writes:
- > Looks like it was a query that was running. once my developer killed it the 
CPU went back down.
- > I'm a little surprised by that, the backend process for that developer 
wasn't taking up a lot of CPU,
- > just the postmaster itself.
- 
- The backtrace you showed was most definitely from a backend, not the
- postmaster.  I think you misidentified the process.  On some platforms
- "ps" isn't tremendously helpful about telling them apart ...
- 
-   regards, tom lane
- 

wow, hmm, if i can't trust linux ps, what can i trust!  =)

Thanks. that would clear it up.

Dave

-- 
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] Postmaster taking 100% of the CPU

[Tom Lane]

David Kerr  writes:
> Looks like it was a query that was running. once my developer killed it the 
> CPU went back down.
> I'm a little surprised by that, the backend process for that developer wasn't 
> taking up a lot of CPU,
> just the postmaster itself.

The backtrace you showed was most definitely from a backend, not the
postmaster.  I think you misidentified the process.  On some platforms
"ps" isn't tremendously helpful about telling them apart ...

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [ANNOUNCE] [pgeu-general] PGDay.EU 2009 - approaching fast!

[Dave Page]

On Mon, Oct 26, 2009 at 2:12 PM, Raymond O'Donnell  wrote:
> On 20/10/2009 12:52, Thom Brown wrote:
>
>> And there's special hotel rates at the Kyriad Hotel, in Porte d'Ivry
>> for those attending the conference.  See http://2009.pgday.eu/hotels
>> for details.
>
> I gather from another Irish attendee that this one is booked out now.

For info, there are also Postgres people staying at the following
hotels. I have no idea of the quality of these though, and there are
no special deals for the conference:

Best Western Nouvel Orleans
25 Avenue Du General Leclerc, Paris, France, 75014
33 (1) 43278020

Hotel Sophie Germaine
12 rue Sophie Germain, Paris, France
33-1-43-21-43-75


-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
PGDay.EU 2009 Conference: http://2009.pgday.eu/start

-- 
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] Why does pg_dump set default_with_oids to true?

[Tom Lane]

Alvaro Herrera  writes:
> Kynn Jones escribió:
>> The documentation deprecates setting this variable to true.  Why does
>> pg_dump do it?

> To dump tables that were created WITH OIDS.

What is deprecated is the use of the WITH OIDS feature, not the variable
;-).  But it is not pg_dump's charter to enforce that deprecation.

Maybe what you are asking is why it uses SET default_with_oids rather
than attaching WITH OIDS to the CREATE TABLE commands.  That was done
as a compatibility measure so that the dump files would still be
readable by PG versions that didn't understand WITH OIDS --- they'd
reject the SET commands but still accept the CREATE TABLE commands.
By the same token the files are a shade more portable to other DBMSes
than they would be with WITH OIDS clauses.  Neither of these arguments
is hugely compelling, perhaps, but avoiding the use of a variable isn't
very compelling either ...

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Logging statements with errors in PostgreSQL 8.1

[John R Pierce]

Radcon Entec wrote:
Is there any way to get a PostgreSQL 8.1 installation to report the 
statements that caused the error, as the 8.3 installation does? 


see 
http://www.postgresql.org/docs/8.1/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT   
specifically, log_statement, and log_line_prefix 



afaik, these options are just about identical from 8.1 to 8.3, so look 
at your 8.3 postgresql.conf file, and you should be able to copy those 
options to the 8.1 file.





--
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] Logging statements with errors in PostgreSQL 8.1

[Adrian Klaver]

On Monday 26 October 2009 5:48:42 am Radcon Entec wrote:
> Greetings!
>
> I am using PostgreSQL 8.3, as are most of our customers.  But we still have
> at least one who is using PostgreSQL 8.1.  The log files from that customer
> are showing repeated syntax errors, but they are not giving me any
> information about where the errors are coming from.  Here's a sample from
> an old 8.1 installation on my machine:
>
> 2009-06-02 13:26:32 user postgres ERROR:  syntax error at or near "OWNED"
> at character 39 2009-06-02 13:26:32 user postgres ERROR:  syntax error at
> or near "OWNED" at character 45 2009-06-02 13:26:32 user postgres ERROR: 
> syntax error at or near "OWNED" at character 62 2009-06-02 13:26:32 user
> postgres ERROR:  syntax error at or near "OWNED" at character 53
>
> On the other hand, my PostgreSQL 8.3 installation reports errors like this:
>
> 2009-10-21 15:16:45 EDT ERROR:  relation "schedule_plan" does not exist
> 2009-10-21 15:16:45 EDT STATEMENT:  select max_heating_rev_interval,
> max_cooling_rev_interval, name, high_heating_limit, low_heating_limit,
> high_heating_correction, low_heating_correction, high_cooling_limit,
> low_cooling_limit, high_cooling_correction, low_cooling_correction from
> schedule_plan
>
> Is there any way to get a PostgreSQL 8.1 installation to report the
> statements that caused the error, as the 8.3 installation does?
>
> I notice that the 8.3 log file also reports (frequently) loading the
> plugin_debugger.dll library.  Is the problem that that library was not
> available in 8.1?
>
> Thanks very much!
>
> RobR

http://www.postgresql.org/docs/8.1/interactive/runtime-config-logging.html
See:
log_min_error_statement (string)

-- 
Adrian Klaver
akla...@comcast.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] Postmaster taking 100% of the CPU

[David Kerr]

Looks like it was a query that was running. once my developer killed it the CPU 
went back down.

I'm a little surprised by that, the backend process for that developer wasn't 
taking up a lot of CPU,
just the postmaster itself.

Any idea why that would be?

Thanks

Dave


On Mon, Oct 26, 2009 at 10:30:42AM -0700, David Kerr wrote:
- Postmaster's been spinning at 99/100% for a few hours.
- 
- trying to get an idea what would have caused it.
- 
- 
- I'm on PG 8.3.5 linux.
- 
- Here's the gdb output (I'm not really all that gdb savvy, so if something 
else would let me know)
- 0x08281959 in textin ()
- (gdb) bt
- #0  0x08281959 in textin ()
- #1  0x082c3d9f in InputFunctionCall ()
- #2  0x081755dd in ?? ()
- #3  0x081755dd in ?? ()
- #4  0x08174fd3 in ExecEvalExprSwitchContext ()
- #5  0x081890cd in ExecSubPlan ()
- #6  0x08174ae1 in ?? ()
- #7  0x08175040 in ExecQual ()
- #8  0x0818322c in ExecHashJoin ()
- #9  0x08174680 in ExecProcNode ()
- #10 0x08185f1d in ExecNestLoop ()
- #11 0x081746b0 in ExecProcNode ()
- #12 0x0817ebbb in ExecAppend ()
- #13 0x081745c0 in ExecProcNode ()
- #14 0x081873b1 in ExecSort ()
- #15 0x08174650 in ExecProcNode ()
- #16 0x081875a6 in ExecUnique ()
- #17 0x08174560 in ExecProcNode ()
- #18 0x081873b1 in ExecSort ()
- #19 0x08174650 in ExecProcNode ()
- #20 0x081724c3 in ExecutorRun ()
- #21 0x082133e3 in ?? ()
- #22 0x082147a9 in PortalRun ()
- #23 0x082111f1 in PostgresMain ()
- #24 0x081e363d in ?? ()
- #25 0x081e464f in PostmasterMain ()
- #26 0x08198906 in main ()
- 
- 
- Thanks
- 
- Dave
- 
- -- 
- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
- To make changes to your subscription:
- http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to list user-specific configuration parameters?

[Vick Khera]

On Mon, Oct 26, 2009 at 1:58 PM, Tim Landscheidt  
wrote:
> Hi,
>
> how can I list the user-specific configuration parameters,
> i. e. those set by "ALTER ROLE name SET ..."?

I've been using "pg_dumpall --globals-only" to view them, but it is
clearly sub-optimal to do it that way.  I'm sure if you monitored the
queries pg_dumpall ran, you could figure it out.  I'm just too lazy to
do that.

-- 
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] joining an array with a table or...?

[Ivan Sergio Borgonovo]

On Mon, 26 Oct 2009 14:56:26 -0400
Merlin Moncure  wrote:

> On Mon, Oct 26, 2009 at 11:05 AM, Ivan Sergio Borgonovo
>  wrote:
> > To make it more concrete I came up with:
> >
> > select coalesce(u.mail,j.mail) from (
> >  select (array['m...@example1.com','m...@example2.com'])[i] as mail
> >   from generate_series(1,2) i) j
> >   left join users u on upper(u.mail)=upper(j.mail);
> 
> how about this:
> select coalesce(u.mail,j.mail) from
> (
>  values ('m...@example1.com'), ('m...@example2.com')
> ) j(mail)
>  left join users u on upper(u.mail)=upper(j.mail);

Yours is between 4 to 10 times faster excluding time on client side
to escape the strings.

I'll play a bit with client code to see if the advantage is kept.

It looks nicer too.

Currently I'm testing with very few match between input array and
user table.
Will this have different impact on the 2 methods?

thanks

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


-- 
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] Postmaster taking 100% of the CPU

[Vick Khera]

On Mon, Oct 26, 2009 at 1:30 PM, David Kerr  wrote:
> Postmaster's been spinning at 99/100% for a few hours.
>

What does "select * from pg_stat_activity" show you?   Look for your
long(est) running query.

-- 
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] [ANNOUNCE] [pgeu-general] PGDay.EU 2009 - approaching fast!

[Peter Geoghegan]

> I gather from another Irish attendee that this one is booked out now.

Yes, it was booked out, but they said I should ring back the next day,
which suggested that they may have expected some cancellations or
something. In any case, I booked the Kyriad Cachan.

The PgDay EU site says that the Kyriad Cachan is 1.5km from Paris, but
I doubt it - Wikipedia says Cachan is 6.7km from Paris city centre. In
any case, it's near the RER, so it shouldn't be a problem.

Regards,
Peter Geoghegan

-- 
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] [ANNOUNCE] [pgeu-general] PGDay.EU 2009 - approaching fast!

[Raymond O'Donnell]

On 26/10/2009 19:08, Cédric Villemain wrote:
> Le lundi 26 octobre 2009 15:12:10, Raymond O'Donnell a écrit :
>> On 20/10/2009 12:52, Thom Brown wrote:
>>> And there's special hotel rates at the Kyriad Hotel, in Porte d'Ivry
>>> for those attending the conference.  See http://2009.pgday.eu/hotels
>>> for details.
>> I gather from another Irish attendee that this one is booked out now.
> 
> Sure ? You *have to* ask for the ParisTech rooms (there were still 10 rooms 
> available on friday, only for Paristech) Else the hotel is full.

Not sure... I'll pass the word on - thanks for the tip. :-)

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
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 list user-specific configuration parameters?

[Alvaro Herrera]

Tim Landscheidt wrote:
> Hi,
> 
> how can I list the user-specific configuration parameters,
> i. e. those set by "ALTER ROLE name SET ..."?

Get them from the pg_authid catalog.

8.5 alpha2 has a new \drds command in psql for that purpose.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
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] [ANNOUNCE] [pgeu-general] PGDay.EU 2009 - approaching fast!

[Cédric Villemain]

Le lundi 26 octobre 2009 15:12:10, Raymond O'Donnell a écrit :
> On 20/10/2009 12:52, Thom Brown wrote:
> > And there's special hotel rates at the Kyriad Hotel, in Porte d'Ivry
> > for those attending the conference.  See http://2009.pgday.eu/hotels
> > for details.
> 
> I gather from another Irish attendee that this one is booked out now.

Sure ? You *have to* ask for the ParisTech rooms (there were still 10 rooms 
available on friday, only for Paristech) Else the hotel is full.

> 
> Ray.
> 

-- 
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org


signature.asc
Description: This is a digitally signed message part.

Re: [GENERAL] Why does pg_dump set default_with_oids to true?

[Alvaro Herrera]

Kynn Jones escribió:
> I've noticed that the dumps generated by pg_dump set the parameter
> default_with_oids to true in various places (separated by setting it back to
> false in-between).
> 
> This happens even for databases whose creation and maintenance did not
> involve any explicit setting of this parameter.
> 
> The documentation deprecates setting this variable to true.  Why does
> pg_dump do it?

To dump tables that were created WITH OIDS.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Logging statements with errors in PostgreSQL 8.1

[Radcon Entec]

Greetings!

I am using PostgreSQL 8.3, as are most of our customers.  But we still have at 
least one who is using PostgreSQL 8.1.  The log files from that customer are 
showing repeated syntax errors, but they are not giving me any information 
about where the errors are coming from.  Here's a sample from an old 8.1 
installation on my machine:

2009-06-02 13:26:32 user postgres ERROR:  syntax error at or near "OWNED" at 
character 39
2009-06-02 13:26:32 user postgres ERROR:  syntax error at or near "OWNED" at 
character 45
2009-06-02 13:26:32 user postgres ERROR:  syntax error at or near "OWNED" at 
character 62
2009-06-02 13:26:32 user postgres ERROR:  syntax error at or near "OWNED" at 
character 53

On the other hand, my PostgreSQL 8.3 installation reports errors like this:

2009-10-21 15:16:45 EDT ERROR:  relation "schedule_plan" does not exist
2009-10-21 15:16:45 EDT STATEMENT:  select max_heating_rev_interval, 
max_cooling_rev_interval, name, high_heating_limit, low_heating_limit, 
high_heating_correction, low_heating_correction, high_cooling_limit, 
low_cooling_limit, high_cooling_correction, low_cooling_correction from 
schedule_plan

Is there any way to get a PostgreSQL 8.1 installation to report the statements 
that caused the error, as the 8.3 installation does?  

I notice that the 8.3 log file also reports (frequently) loading the 
plugin_debugger.dll library.  Is the problem that that library was not 
available in 8.1?

Thanks very much!

RobR  



  

Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?

[Timothy Madden]

On Mon, Oct 26, 2009 at 6:37 AM, Pavel Stehule wrote:

> 2009/10/26 Tom Lane :
> > Alvaro Herrera  writes:
> >> SQL/PSM is a different beast than all the rest of the PLs, because it is
> >> standard, so I am sure that we will want to implement the standard
> >> syntax (no string literal) when we have SQL/PSM.  But implementing no-
> >> string-literals before we get full SQL/PSM support would be pointless,
> >> because there are so many other things that are not standard in that
> >> area.  Simply removing the quotes (which is what you are requesting)
> >> would not take our standards compliance much further.
> >
> > [ after re-reading the spec a little bit ... ]
> >
> > One interesting point here is that I don't think the spec suggests
> > that SQL/PSM can be written in-line in the CREATE FUNCTION statement
> > at all.  What I see (at least in SQL99) is
> >
> >  ::=
> >  CREATE 
> >
> >  ::=
> >  {  |  designator> }
> >
> >
> >
> >  ::=
> >  FUNCTION 
> >
> >
> >
> >[  ]
> >
> >  ::=
> >
> >  | 
> >
> >  ::= 
> >
> > and  seems to allow one (count em, one) SQL DDL
> > or DML statement.  So per spec, essentially every interesting case
> > requires an .  We could possibly support the
> > single-SQL-statement case without any quotes --- at least, it doesn't
> > obviously break clients to do that; handling it inside the backend still
> > seems nontrivial.  But it's not clear to me that that case is useful
> > enough to be worth the trouble.
> >
>
> it is not correct. When you would to use more statements, then you can
> to use BEGIN ... END;
>
> so CREATE FUNCTION foo(...)
> RETURNS int AS
> BEGIN
>  DECLARE x int;
>  SET x = 10;
>  RETURN x;
> END;
>
> is correct.
>
> CREATE FUNCTION foo(...)
> RETURNS int AS
>  RETURN x;
>
> is correct too. The block is optional in SQL/PSM.
>
> http://www.postgres.cz/index.php/SQL/PSM_Manual
>
> What I known, other DBMS have to solve this complications too. Next
> possibility is using some special symbol for ending parser like
> DELIMITER.
>
> Actually we have a independent parsers for SQL and PL languages. It
> has some adventages:
> a) we could to support more PL languages
> b) PL parser are relative small, SQL parser is relative clean
>
> If we integrate some language to main parser, then we have to able to
> block some parts of parser dynamicky - because some functionality
> should be invisible from some PL - SQL/PSM FOR statement has different
> syntax than PL/pgSQL FOR statement. I thing, so this is possible - but
> it uglify parser. If somebody found way how to do extendable parser in
> bison, then we could to go far, but actually I don't advantages change
> anything on current syntax.
>
> Regards
> Pavel Stehule
>
>

Thank you all for your considerate replies.

Why am I wielding the wrong argument ? My argument is standards conformance.
Because there are many other non-standard expressions in the current syntax
?
So my issue is just one more on the list ...

Full SQL/PSM support seems pretty far; why is it needed in order to also
consider
the non string-literal function definitions for language SQL functions ? I
rather see
removing quotes as the first step towards SQL/PSM, then the last ...

We are getting philosophical, but agreement is still necessary for a patch
from what
I know, especially that the effort to understand the project is
comprehensive. That
is why I was concerned about agreement.

The DELIMITER artefact is also misplaced in my opinion; what is the use for
it ?
The BEGIN ... END syntax is pretty clear ...
DELIMITER is just to keep the client-side parsing / input simple ?

For other languages the string literal syntax is ok, my issue only concerns
LANGUAGE SQL functions 

Thank you,
Timothy Madden

Re: [GENERAL] joining an array with a table or...?

[Merlin Moncure]

On Mon, Oct 26, 2009 at 11:05 AM, Ivan Sergio Borgonovo
 wrote:
> To make it more concrete I came up with:
>
> select coalesce(u.mail,j.mail) from (
>  select (array['m...@example1.com','m...@example2.com'])[i] as mail
>   from generate_series(1,2) i) j
>   left join users u on upper(u.mail)=upper(j.mail);

how about this:
select coalesce(u.mail,j.mail) from
(
 values ('m...@example1.com'), ('m...@example2.com')
) j(mail)
 left join users u on upper(u.mail)=upper(j.mail);

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why does pg_dump set default_with_oids to true?

[Joshua D. Drake]

On Mon, 2009-10-26 at 11:04 -0400, Kynn Jones wrote:
> I've noticed that the dumps generated by pg_dump set the parameter
> default_with_oids to true in various places (separated by setting it
> back to false in-between).
> 
> 
> This happens even for databases whose creation and maintenance did not
> involve any explicit setting of this parameter.
> 
> 
> The documentation deprecates setting this variable to true.  Why does
> pg_dump do it?

It is picking up objects that have OIDS.

Joshua D. Drake

> 
> 
> TIA!
> 
> 
> Kynn
> 
> 


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - 
Salamander


-- 
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 list user-specific configuration parameters?

[Tim Landscheidt]

Hi,

how can I list the user-specific configuration parameters,
i. e. those set by "ALTER ROLE name SET ..."?

Tim


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postmaster taking 100% of the CPU

[David Kerr]

Postmaster's been spinning at 99/100% for a few hours.

trying to get an idea what would have caused it.


I'm on PG 8.3.5 linux.

Here's the gdb output (I'm not really all that gdb savvy, so if something else 
would let me know)
0x08281959 in textin ()
(gdb) bt
#0  0x08281959 in textin ()
#1  0x082c3d9f in InputFunctionCall ()
#2  0x081755dd in ?? ()
#3  0x081755dd in ?? ()
#4  0x08174fd3 in ExecEvalExprSwitchContext ()
#5  0x081890cd in ExecSubPlan ()
#6  0x08174ae1 in ?? ()
#7  0x08175040 in ExecQual ()
#8  0x0818322c in ExecHashJoin ()
#9  0x08174680 in ExecProcNode ()
#10 0x08185f1d in ExecNestLoop ()
#11 0x081746b0 in ExecProcNode ()
#12 0x0817ebbb in ExecAppend ()
#13 0x081745c0 in ExecProcNode ()
#14 0x081873b1 in ExecSort ()
#15 0x08174650 in ExecProcNode ()
#16 0x081875a6 in ExecUnique ()
#17 0x08174560 in ExecProcNode ()
#18 0x081873b1 in ExecSort ()
#19 0x08174650 in ExecProcNode ()
#20 0x081724c3 in ExecutorRun ()
#21 0x082133e3 in ?? ()
#22 0x082147a9 in PortalRun ()
#23 0x082111f1 in PostgresMain ()
#24 0x081e363d in ?? ()
#25 0x081e464f in PostmasterMain ()
#26 0x08198906 in main ()


Thanks

Dave

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Operational performance: one big table versus many smaller tables

[David Wall]

If I have various record types that are "one up" records that are 
structurally similar (same columns) and are mostly retrieved one at a 
time by its primary key, is there any performance or operational benefit 
to having millions of such records split across multiple tables (say by 
their application-level purpose) rather than all in one big table? 

I am thinking of PG performance (handing queries against multiple tables 
each with hundreds of thousands or rows, versus queries against a single 
table with millions of rows), and operational performance (number of WAL 
files created, pg_dump, vacuum, etc.).


If anybody has any tips, I'd much appreciate it.

Thanks,
David

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Defining roles

[Michael Gould]

In our system we have a hybrid security system. We have tables that I want
to make sure that based on membership in a group that update and/or delete
is not allowed to a specific group. We also have application level security
which is much more granular and is much more job function based.  Our
application is a transportation application, so a user might have insert,
update and delete in order entry but a dispatcher would not have the ability
to delete a order, they must cancel it with a reason code.  These would both
be part of the same ROLE in the database.  Trying to maintain the database
to match the application security would become cumbersome for our customers.

Now for the question, if I specifically revoke a update or delete on a per
table basis for a role to I still have to specifically define what security
attributes they have access on? If this doesn't work, would I give access to
the schema and then just specifically revoke the update or delete
functionality for just the tables I'm looking to protect.

Best Regards

Mike Gould



-- 
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] joining an array with a table or...?

[Ivan Sergio Borgonovo]

On Mon, 26 Oct 2009 14:15:26 +0100
Ivan Sergio Borgonovo  wrote:

> I've a list of emails and a list of users (with emails).
> 
> If the list of emails was already inside a table
> 
> create table mails (
>  mail varchar(64)
> );
> 
> create table users (
>   name varchar(127),
>   mail varchar(64)
> );
> 
> I'd do:
> select coalesce(u.mail, m.mail) from mails left join users on
> u.mail=m.mail;
> 
> Now mails are into a php array and they may be in the range of 5000
> but generally less.
> 
> The final query will be something like
> insert into mailqueue (qid, uid, mail, ...) select ...
> 
> and since some fields are pseudo random sequences computed from a
> serial, it would be "clean" to do it just in one query.

> Any clean technique?

To make it more concrete I came up with:

select coalesce(u.mail,j.mail) from (
  select (array['m...@example1.com','m...@example2.com'])[i] as mail
   from generate_series(1,2) i) j
   left join users u on upper(u.mail)=upper(j.mail);

but I sincerely dislike it.

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Why does pg_dump set default_with_oids to true?

[Kynn Jones]

I've noticed that the dumps generated by pg_dump set the parameter
default_with_oids to true in various places (separated by setting it back to
false in-between).

This happens even for databases whose creation and maintenance did not
involve any explicit setting of this parameter.

The documentation deprecates setting this variable to true.  Why does
pg_dump do it?

TIA!

Kynn

Re: [GENERAL] cursor MOVE vs OFFSET in SELECT

[Alvaro Herrera]

silly escribió:
> 2009/10/26 Grzegorz Jaśkiewicz :
> >
> >
> > On Mon, Oct 26, 2009 at 10:30 AM, silly  wrote:
> >>
> >> Suppose that you have a query, say $sql_query, which is very
> >> complicated and produces many rows. Which of the following is going to
> >> be faser:
> >>
> >>    $sql_query OFFSET 3000 LIMIT 12;
> >>
> >> or
> >>
> >>    BEGIN;
> >>    DECLARE cur1 CURSOR FOR $sql_query;
> >>    MOVE 3000 IN cur1;
> >>    FETCH 12 FROM cur1;
> >>    COMMIT;
> >>
> >> Naturally, the former cannot be slower than the latter. So my question
> >> essentially is whether the MOVE operation on a cursor is
> >> (significantly) slower that a OFFSET on the SELECT.
> >
> >
> > OFFSET/LIMIT. Afaik cursor always fetches everything.
> 
> Well, in my experiments they always perform the same. I suspect that
> the way SELECT/OFFSET is implemented is not much different than
> cursor/MOVE.

The cursor could choose a different plan due to the "fast startup"
behavior that Pavel alludes to.  You can actually change that by setting
the cursor_tuple_fraction parameter.  Whether this plan is faster or
slower than the other one is problem dependent.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
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] drop view and recreate - for sync

[Vick Khera]

On Fri, Oct 23, 2009 at 6:44 PM, Sydney Puente  wrote:
> Ah I see what you mean - thanks very much that is v helpful!
> Yes the copy will be read-only.
> Will have 3 tables of data, being read (readonly) and in the background
> Will have 3 shadow tables populated from an unreliable db over an unreliable
> network.
> not quite sure how I can "insert all the rows" in sql.
> have postgres 8.03 BTW.
>

If your network is unreliable, then perhaps the thing to do is have
your program first fetch all the new data over that network into tab
delimited files, one per table.  Then to insert all your data, just
use the "COPY" command in postgres to read it all as one hunk of data.
 This will be your fastest, most reliable way of loading the data in
minimal time.  Your only other option is to issue 100,000 "INSERT"
statements, which will take much longer.

-- 
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] [ANNOUNCE] [pgeu-general] PGDay.EU 2009 - approaching fast!

[Raymond O'Donnell]

On 20/10/2009 12:52, Thom Brown wrote:

> And there's special hotel rates at the Kyriad Hotel, in Porte d'Ivry
> for those attending the conference.  See http://2009.pgday.eu/hotels
> for details.

I gather from another Irish attendee that this one is booked out now.

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] joining an array with a table or...?

[Ivan Sergio Borgonovo]

I've a list of emails and a list of users (with emails).

If the list of emails was already inside a table

create table mails (
 mail varchar(64)
);

create table users (
  name varchar(127),
  mail varchar(64)
);

I'd do:
select coalesce(u.mail, m.mail) from mails left join users on
u.mail=m.mail;

Now mails are into a php array and they may be in the range of 5000
but generally less.

The final query will be something like
insert into mailqueue (qid, uid, mail, ...) select ...

and since some fields are pseudo random sequences computed from a
serial, it would be "clean" to do it just in one query.

Any clean technique?

thanks

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


-- 
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] cursor MOVE vs OFFSET in SELECT

[Pavel Stehule]

2009/10/26 Grzegorz Jaśkiewicz :
>
>
> On Mon, Oct 26, 2009 at 10:30 AM, silly  wrote:
>>
>> Suppose that you have a query, say $sql_query, which is very
>> complicated and produces many rows. Which of the following is going to
>> be faser:
>>
>>    $sql_query OFFSET 3000 LIMIT 12;
>>
>> or
>>
>>    BEGIN;
>>    DECLARE cur1 CURSOR FOR $sql_query;
>>    MOVE 3000 IN cur1;
>>    FETCH 12 FROM cur1;
>>    COMMIT;
>>
>> Naturally, the former cannot be slower than the latter. So my question
>> essentially is whether the MOVE operation on a cursor is
>> (significantly) slower that a OFFSET on the SELECT.
>
>
> OFFSET/LIMIT. Afaik cursor always fetches everything.

OFFSET/LIMIT process same rows as cursor. There could be only one
difference. Cursors uses strategy "fast first row", SELECT uses
"minimum complete query time".

Regards
Pavel Stehule

>
>
> --
> GJ
>

-- 
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] cursor MOVE vs OFFSET in SELECT

[silly8888]

2009/10/26 Grzegorz Jaśkiewicz :
>
>
> On Mon, Oct 26, 2009 at 10:30 AM, silly  wrote:
>>
>> Suppose that you have a query, say $sql_query, which is very
>> complicated and produces many rows. Which of the following is going to
>> be faser:
>>
>>    $sql_query OFFSET 3000 LIMIT 12;
>>
>> or
>>
>>    BEGIN;
>>    DECLARE cur1 CURSOR FOR $sql_query;
>>    MOVE 3000 IN cur1;
>>    FETCH 12 FROM cur1;
>>    COMMIT;
>>
>> Naturally, the former cannot be slower than the latter. So my question
>> essentially is whether the MOVE operation on a cursor is
>> (significantly) slower that a OFFSET on the SELECT.
>
>
> OFFSET/LIMIT. Afaik cursor always fetches everything.

Well, in my experiments they always perform the same. I suspect that
the way SELECT/OFFSET is implemented is not much different than
cursor/MOVE.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Allowed types in embedded SQL, ecpg

[Hans-Michael Stahl]

When reading the documentation (Section 32.6.3. Different types of host
variables) I find description only for basic date types. It is not
described, how I can retrieve, for example, a big integer, byte varying,
bit varying, or text data type in embedded SQL.

Is this not possible, or is it only not described, or do I read at the
wrong place?



-- 
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] cursor MOVE vs OFFSET in SELECT

[Grzegorz Jaśkiewicz]

On Mon, Oct 26, 2009 at 10:30 AM, silly  wrote:

> Suppose that you have a query, say $sql_query, which is very
> complicated and produces many rows. Which of the following is going to
> be faser:
>
>$sql_query OFFSET 3000 LIMIT 12;
>
> or
>
>BEGIN;
>DECLARE cur1 CURSOR FOR $sql_query;
>MOVE 3000 IN cur1;
>FETCH 12 FROM cur1;
>COMMIT;
>
> Naturally, the former cannot be slower than the latter. So my question
> essentially is whether the MOVE operation on a cursor is
> (significantly) slower that a OFFSET on the SELECT.
>


OFFSET/LIMIT. Afaik cursor always fetches everything.


-- 
GJ

[GENERAL] cursor MOVE vs OFFSET in SELECT

[silly8888]

Suppose that you have a query, say $sql_query, which is very
complicated and produces many rows. Which of the following is going to
be faser:

$sql_query OFFSET 3000 LIMIT 12;

or

BEGIN;
DECLARE cur1 CURSOR FOR $sql_query;
MOVE 3000 IN cur1;
FETCH 12 FROM cur1;
COMMIT;

Naturally, the former cannot be slower than the latter. So my question
essentially is whether the MOVE operation on a cursor is
(significantly) slower that a OFFSET on the SELECT.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general