[ADMIN] Trouble with recovering disc space

Dear list,

I have the following issue with recovering disc space...

Interscan Web Security Suite(Trend Micro gateway antivirus appl.) uses
Postgres database to store access logs(to the internet) in table tb_url_usage.

This table occupies approximately 12GB based on oid:

-rw------- 1 iscan iscan 1073741824 May 15 14:37 48495427
-rw------- 1 iscan iscan 1073741824 May 15 02:00 48495427.1
-rw------- 1 iscan iscan 1073741824 May 15 11:25 48495427.10
-rw------- 1 iscan iscan 1073741824 May 14 02:01 48495427.2
-rw------- 1 iscan iscan 1073741824 May 14 02:01 48495427.3
-rw------- 1 iscan iscan 1073741824 May 14 13:34 48495427.4
-rw------- 1 iscan iscan 1073741824 May 14 13:34 48495427.5
-rw------- 1 iscan iscan 1073741824 May 15 14:37 48495427.6
-rw------- 1 iscan iscan 1073741824 May 15 13:19 48495427.7
-rw------- 1 iscan iscan 1073741824 May 15 11:58 48495427.8
-rw------- 1 iscan iscan 1073741824 May 15 11:30 48495427.9
-rw------- 1 iscan iscan 399007744 May 15 09:07 48495427.11

Now, if I run a DELETE(some rows) on this table after that VACUUM FULL it
does not return any space to the operating system. These files remains. If I
am right, VACUUM FULL should decrease the physical size of the table, am I?
May I delete som of these files? Or how can I free up space on filesystem ?

I am running Postgresql version 8.0 on Fedora Core 4. If you need some
additional information I send you...

Please any advise could help!

Kind regards,

chris

--- reklama -----------------------------------------------------
Vieš, čo Ťa dnes čaká? Pozri si horoskop!
http://horoskop.zoznam.sk

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

Re: [PERFORM] I/O on select count(*)

On Thu, 15 May 2008, Luke Lonergan wrote:
> BTW ­ we¹ve removed HINT bit checking in Greenplum DB and improved the
> visibility caching which was enough to provide performance at the same level
> as with the HINT bit optimization, but avoids this whole ³write the data,
> write it to the log also, then write it again just for good measure²
> behavior.

This sounds like a good option. I believe I suggested this a few months
ago, however it was rejected because in the worst case (when the hints are
not cached), if you're doing an index scan, you can do twice the number of
seeks as before.

http://archives.postgresql.org/pgsql-performance/2007-12/msg00217.php

The hint data will be four bits per tuple plus overheads, so it could be
made very compact, and therefore likely to stay in the cache fairly well.
Each tuple fetched would have to be spaced really far apart in the
database table in order to exhibit the worst case, because fetching a page
of hint cache will cause 64kB or so of disc to appear in the disc's
read-ahead buffer, which will be equivalent to 128MB worth of database
table (assuming eight tuples per block and no overhead). As soon as you
access another tuple in the same 128MB bracket, you'll hit the disc
read-ahead buffer for the hints.

On balance, to me it still seems like a good option.

Matthew

--
Those who do not understand Unix are condemned to reinvent it, poorly.
-- Henry Spencer
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] I/O on select count(*)

Matthew Wakeling wrote:
> Is it really safe to update the hint bits in place? If there is a power
> cut in the middle of writing a block, is there a guarantee from the disc
> that the block will never be garbled?

Don't know, to be honest. We've never seen any reports of corrupted data
that would suggest such a problem, but it doesn't seem impossible to me
that some exotic storage system might do that.

> Is there a way to make a shortcut and have the hint bits written the
> first time the data is written to the table? One piece of obvious
> low-hanging fruit would be to enhance step five above, so that the
> bgwriter or checkpoint that writes the data to the database table checks
> the pg_clog and writes the correct hint bits.

Yep, that's an idea that's been suggested before. In fact, I seem to
remember a patch to do just that. Don't remember what happened to it,

> In fact, if the tuple's
> creating transaction has aborted, then the tuple can be vacuumed right
> there and then before it is even written.

Not if you have any indexes on the table. To vacuum, you'll have to scan
all indexes to remove pointers to the tuple.

> However, this idea does not deal well with bulk data loads, where the
> data is checkpointed before transaction is committed or aborted.

Yep, that's the killer :-(.

--
Heikki Linnakangas
EnterpriseDB

http://www.enterprisedb.com

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

[HACKERS] SSL and USER_CERT_FILE

I am using PostgreSQL's SSL support and the conventions for the key and
certifications don't make sense from the client perspective. Especially
under Windows.

I am proposing a few simple changes:

Adding two API
void PQsetSSLUserCertFileName(char *filename)
{
user_crt_filename = strdup(filename);
}
PQsetSSLUserKeyFileName(char *filename)
{
user_key_filename = strdup(filename);
}

Adding two static vars in fe-secure.c

char *user_key_filename=NULL;
char *user_crt_filename=NULL;

In client_cert_cb(...)

Add:
if(user_crt_filename)
strncpy(fnbuf, sizeof(fnbuf), user_crt_filename);
else
snprintf(fnbuf, sizeof(fnbuf), "%s/%s", homedir, USER_CERT_FILE);

and:

if(user_key_filename)
strncpy(fnbuf, sizeof(fnbuf), user_key_filename);
else
snprintf(fnbuf, sizeof(fnbuf), "%s/%s", homedir, USER_KEY_FILE);

The purpose of these changes is to make it easier to configure SSL in an
application which uses libpq.

Any comments?

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

Re: [GENERAL] Howto return values from a function

> What exactly about the documentation isn't clear?
I would have liked a few more examples... but that is perhaps just me.

> Like the documentation says: SETOF sometype.
Ah, so I just create my own type with "CREATE TYPE ..." and use that
type in the function.

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

Re: [PATCHES] Patch to change psql default banner v6

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Ah, OK. I had forgotten. Here is the new output:
>
> > $ sql test
> > psql (8.4devel) Type "help" for help.
>
> > test=> help
>
> You are being unreasonably cryptic here. What happens when there
> is optional output --- ie, version mismatch warning and/or SSL info?

Oh, good point. Let me look at that. Thanks. You prefer:

$ sql test
psql (8.4devel)
Type "help" for help.

test=> help

That looked so sparse to me.

--
Bruce Momjian <bruce@momjian.us>

http://momjian.us

EnterpriseDB

http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

Re: [PERFORM] I/O on select count(*)

On Wed, 14 May 2008, Alvaro Herrera wrote:
> Hint bits are used to mark tuples as created and/or deleted by
> transactions that are know committed or aborted. To determine the
> visibility of a tuple without such bits set, you need to consult pg_clog
> and possibly pg_subtrans, so it is an expensive check.

So, as I understand it, Postgres works like this:

1. You begin a transaction. Postgres writes an entry into pg_clog.
2. You write some tuples. Postgres writes them to the WAL, but doesn't
bother fsyncing.
3. At some point, the bgwriter or a checkpoint may write the tuples to the
database tables, and fsync the lot.
4. You commit the transaction. Postgres alters pg_clog again, writes that
to the WAL, and fsyncs the WAL.
5. If the tuples hadn't already made it to the database tables, then a
checkpoint or bgwriter will do it later on, and fsync the lot.
6. You read the tuples. Postgres reads them from the database table, looks
in pg_clog, notices that the transaction has been committed, and
writes the tuples to the database table again with the hint bits set.
This write is not WAL protected, and is not fsynced.

This seems like a good architecture, with some cool characteristics,
mainly that at no point does Postgres have to hold vast quantities of data
in memory. I have two questions though:

Is it really safe to update the hint bits in place? If there is a power
cut in the middle of writing a block, is there a guarantee from the disc
that the block will never be garbled?

Is there a way to make a shortcut and have the hint bits written the first
time the data is written to the table? One piece of obvious low-hanging
fruit would be to enhance step five above, so that the bgwriter or
checkpoint that writes the data to the database table checks the pg_clog
and writes the correct hint bits. In fact, if the tuple's creating
transaction has aborted, then the tuple can be vacuumed right there and
then before it is even written. For OLTP, almost all the hint bits will be
written first time, and also the set of transactions that will be looked
up in the pg_clog will be small (the set of transactions that were active
since the last checkpoint), so its cache coherency will be good.

However, this idea does not deal well with bulk data loads, where the data
is checkpointed before transaction is committed or aborted.

Matthew

--
Now, you would have thought these coefficients would be integers, given that
we're working out integer results. Using a fraction would seem really
stupid. Well, I'm quite willing to be stupid here - in fact, I'm going to
use complex numbers. -- Computer Science Lecturer

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

Re: [GENERAL] Howto return values from a function

On Thu, May 15, 2008 at 02:25:36PM +0200, A B wrote:
> I'm still trying to learn to write plpgsql functions, but I find the
> docs a little short on examples on how to return stuff from a
> function. I'm very grateful for any help on this.

What exactly about the documentation isn't clear?

http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

> CREATE FUNCTION foo() RETURNS ???? AS

> but what do I write instead of ????

Like the documentation says: SETOF sometype.

> I'm nto sure here, but It seems to mee that there are two other ways
> of doing case 2.

How you generate the results is up to you. when you have them you
either use RETURN NEXT or RETURN QUERY to return them to the caller.

> 3) In the third case, I want to create the values I return by joining
> many values. Something like this
> CREATE FUNCTION foo() RETURNS ???? AS

You can always use out parameters if you feel better about it:

CREATE FUNCTION foo(col1 int4 OUT, col2 text OUT, ...) AS ...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org>

http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Re: [GENERAL] problem with importing dbf file into postgresql using navicat on mac OS X

On May 12, 2008, at 1:28 PM, Mathias Ghys wrote:

> Error Message: ERROR: invalid byte sequence for encoding "UTF8":
> 0xe96f70
> HINT: This error can also happen if the byte sequence does not
> match the encoding expected by the server, which is controlled by
> "client_encoding".
>
> My postgresql database is UTF-8 encoded. With importing I get the
> following data:
>
> http://server58.dedicatedusa.com/~mathias/mathias/datatypes.tiff
>
> do you guys have any idea what i've been doing wrong and how I could
> get the *.dbf files into my postgresql database?

I think the problem is that Navicat sets your client connection to
UTF-8 but the data you are importing from the file is not UTF-8.
Navicat should have a way for you to change your client encoding to
match what is in the file. The main problem is you need to know
exactly what encoding is being used in the file.

If the dbf file is really broken (invalid sequences no mater how it is
encoded) then you'll need to fix that first.

John DeSoi, Ph.D.

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

[GENERAL] Howto return values from a function

I'm still trying to learn to write plpgsql functions, but I find the
docs a little short on examples on how to return stuff from a
function. I'm very grateful for any help on this.

There are some basic cases I've identified.

1) when I want to return all records found by a query , like this
CREATE FUNCTION foo() RETURNS ???? AS
BEGIN
RETURN QUERY SELECT a,b,c,d,... FROM T1,T2,... WHERE ....;
END;

but what do I write instead of ????

2) when I select stuff, iterate over the result before returning it
CREATE FUNCTION foo() RETURNS ???? AS
BEGIN
FOR result IN SELECT .....
LOOP
do something with result...
RETURN NEXT result
END LOOP;
END

I'm nto sure here, but It seems to mee that there are two other ways
of doing case 2.
2a) run the query once more and RETURN QUERY at the end instead of the
RETURN NEXT statement.
2b) store the result in some temporary storage... (I'm not sure how,
it's just a feeling I get that this should be possible, I might be
completely wrong) and then return the whole result a once.
As usual, what do I write instead of ????

3) In the third case, I want to create the values I return by joining
many values. Something like this
CREATE FUNCTION foo() RETURNS ???? AS
BEGIN
myvar := .....
myvar2 := ....
FOR result IN SELECT ...
LOOP
FOR result2 IN SELECT .....
LOOP
RETURN NEXT ????????????;
END LOOP;
END LOOP;
RETURN
END

The ?????????????? part should perhaps be something like rows with the values
[ myvar, myvar2, result.f1, result.f2, result2.f5, result2.f7 ]

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

Re: [PERFORM] which ext3 fs type should I use for postgresql

On Thu, 15 May 2008, david@lang.hm wrote:
> IIRC postgres likes to do 1M/file, which isn't very largeas far as the -T
> setting goes.

ITYF it's actually 1GB/file.

> think twice about this. ext2/3 get slow when they fill up (they have
> fragmentation problems when free space gets too small), this 5% that only
> root can use also serves as a buffer against that as well.

It makes sense to me that the usage pattern of Postgres would be much less
susceptible to causing fragmentation than normal filesystem usage. Has
anyone actually tested this and found out?

Matthew

--
Isn't "Microsoft Works" something of a contradiction?

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

Re: [PERFORM] which ext3 fs type should I use for postgresql

On Thu, 15 May 2008, Matthew Wakeling wrote:

> On Thu, 15 May 2008, Philippe Amelant wrote:
>> using mkfs.ext3 I can use "-T" to tune the filesytem
>>
>> mkfs.ext3 -T fs_type ...
>>
>> fs_type are in /etc/mke2fs.conf (on debian)
>
> If you look at that file, you'd see that tuning really doesn't change that
> much. In fact, the only thing it does change (if you avoid "small" and
> "floppy") is the number of inodes available in the filesystem. Since Postgres
> tends to produce few large files, you don't need that many inodes, so the
> "largefile" option may be best. However, note that the number of inodes is a
> hard limit of the filesystem - if you try to create more files on the
> filesystem than there are available inodes, then you will get an out of space
> error even if the filesystem has space left.
> The only real benefit of having not many inodes is that you waste a little
> less space, so many admins are pretty generous with this setting.

IIRC postgres likes to do 1M/file, which isn't very largeas far as the -T
setting goes.

> Probably of more use are some of the other settings:
>
> -m reserved-blocks-percentage - this reserves a portion of the filesystem
> that only root can write to. If root has no need for it, you can kill
> this by setting it to zero. The default is for 5% of the disc to be
> wasted.

think twice about this. ext2/3 get slow when they fill up (they have
fragmentation problems when free space gets too small), this 5% that
only root can use also serves as a buffer against that as well.

> -j turns the filesystem into ext3 instead of ext2 - many people say that
> for Postgres you shouldn't do this, as ext2 is faster.

for the partition with the WAL on it you may as well do ext2 (the WAL is
written synchronously and sequentially so the journal doesn't help you),
but for the data partition you may benifit from the journal.

David Lang

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

Re: [GENERAL] Need for help!

On Thu, May 15, 2008 at 3:48 PM, Semi Noob <seminoob@gmail.com> wrote:
>
>
> I set max_connections is 200.

What error message you get when you try with more than 64 clients ?

> 57 seems a small number, according to you, how much tps is normal or fast?

Its difficult to say how much is good. On my laptop for s = 10, c =
40, t = 1000, I get 51 tps. But on a larger 2 CPU, 2 GB, 3 RAID 0
disks for data and a separate disk for xlog, I get 232 tps.

> and what is the different of "shared_buffers" and "effective_cache_size".
>

"shared_buffers" is the size of the buffer pool which Postgres uses to
cache the data blocks.
"effective_cache_size" is usually size of the shared buffer plus
estimate of whatever data OS can cache. Planner uses this
approximation to choose right plan for execution.

http://www.postgresql.org/docs/8.3/interactive/runtime-config-query.html

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

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

Re: [JDBC] Testing JDBC Prepared Statements

Kris Jurka schrieb:

On Wed, 14 May 2008, Daniel Migowski wrote:

Is it possible with the PostgreSQL JDBC driver (leaving the JDBC API space is allowed) to serverside prepare a statement without executing it?

PreparedStatement.getParameterMetaData will prepare a statement without executing it.

Thank you. I also already helped me with something like statement.executeQuery("PREPARY test AS "+myQueryToTest), but your variant does apropriate query parsing, which seem better.

With best regards,
Daniel Migowski

--

 |¯¯|¯¯|    IKOffice GmbH             Daniel Migowski  |  |  |/|                            Mail: dmigowski@ikoffice.de  |  | // |  Nordstr. 10               Tel.: 0441 21 98 89 52  |  | \\ |  26135 Oldenburg           Fax.: 0441 21 98 89 55  |__|__|\|  http://www.ikoffice.de    Mob.: 0176 22 31 20 76

Re: [pgsql-es-ayuda] Consulta SQL

2008/5/15 Miguel Rodríguez Penabad <penabad@gmail.com>:
> El día 15 de mayo de 2008 11:58, Antonio Antonio
> <a2076totonio@yahoo.es> escribió:
>
> Por ello te recomendaría que definieses EXACTAMENTE lo que quieres
> recuperar, y así podríamos ayudarte mejor a solucionar el problema en
> Postgres (en SQL estándar).
>

y con resultados razonables

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 087171157
--
TIP 10: no uses HTML en tu pregunta, seguro que quien responda no podrá leerlo

[pgsql-www] hub.org Nagios monitoring

Hi Marc,

webmaster@postgresql.org is getting spammed with irrelevant nagios
alerts from smc.hub.org. Can you fix that please?

Thanks.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

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

Re: [GENERAL] help

On Tue, May 13, 2008 at 03:30:09PM +0530, Elizabeth George wrote:
> SET CLIENT_ENCODING TO 'UNICODE';
> copy (select 1 as F1) to E'c:\\test.out' csv QUOTE AS E'\xFE' FORCE
> QUOTE F1;
>
> I got error like
>
> ERROR: invalid byte sequence for encoding "UTF8": 0xfe
> HINT: This error can also happen if the byte sequence does not match
> the encoding expected by the server, which is controlled by
> "client_encoding".

Right, because 0xfe is not a valid character in UTF-8. Pick something
else.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org>

http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Re: [GENERAL] Need for help!

Thank you for your answer!
"You did not give CPU and disk info. But still 57 seems a small number.
What I guess is you're running pgbench with scale factor 1 (since you
haven't mentioned scale factor) and that causes extreme contention for
smaller tables with large number of clients."

My CPU is 2CPU: Intel(R) Xeon(TM) CPU 3.20GHz. Disk: disk system is RAID-5; OS CentOS. the number of scale in pgbench initialization is 100. It will be generate 10 000 000 rows in the accounts table. Fill factor is default.
In the other way, I heard that: PostgreSQL working with RAID-10 better than RAID-5 is it right?

       "Regarding maximum number of clients, check your "max_connections" setting."

I set max_connections is 200.

57 seems a small number, according to you, how much tps is normal or fast? and what is the different of "shared_buffers" and "effective_cache_size".

Thank you once more!
Regards,
Semi Noob

2008/5/15 Pavan Deolasee <pavan.deolasee@gmail.com>:

On Tue, May 13, 2008 at 2:43 PM, Semi Noob <seminoob@gmail.com> wrote:
>  But after upgrade the max clients is
> also 64 (?!?) Is this the maximum clients support by program pgbench (my
> server on Linux ver8.2.5, pgbench on Windows - version postgresql is 8.3.1)?
> And the number 57 tps is fast?
>

You did not give CPU and disk info. But still 57 seems a small number.
What I guess is you're running pgbench with scale factor 1 (since you
haven't mentioned scale factor) and that causes extreme contention for
smaller tables with large number of clients.

Regarding maximum number of clients, check your "max_connections" setting.

> Another questions, i heard that PostgreSQL does not support HT Technology,
> is it right?
>

I'm not sure what do you mean by HT, but if it's hyper threading, then
IMO that statement is not completely true. Postgres is not
multi-threaded, so a single process (or connection) may not be able to
use all the CPUs, but as long as there are multiple connections (each
connection corresponds to one backend process), as many CPUs will be
used.

> Last question, i don't understand so much the shmmax, shared_buffers, after
> upgrading my server from 4 GB RAM to 8 GB RAM, first i configure shmmax to
> 2GB, share_buffers to 1GB and start server, it runs, after that i set shmmax
> to 4GB and restart, it fails (?!?). The error logs said that not enough
> share memory!  and final i set shmmax to 3GB and share buffer to 2GB, it
> runs. Don't know why, can you explain?

That doesn't make sense. I am guessing that you are running a 32 bit
OS. 4GB of shmmax won't work on a 32 bit OS.

Thanks,
Pavan

Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

[pgsql-jobs] 2008-05-14 Postgres Database Administrator

I was asked to add this job posting to the LAPUG Blog. I thought I
should also repost the link to pgsql-jobs:

http://pugs.postgresql.org/node/396

--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

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

Re: [BUGS] Problem with TOAST tables when removing TOASTable column

> To make that happen would require (at least) a full table scan. I think
> most people are more interested in DROP COLUMN being a cheap operation
> than in having the space be reclaimed quickly.

> For a comparison point: large field values that don't happen to get
> toasted don't vanish immediately, either.

I agree DROP COLUMN should be cheap and I don't really expect it to happend immediately,
but shouldn't VACUUM FULL clean it up?

Wojtek Strzalka

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

[HACKERS] CVS HEAD warnings fixed

Index: src/bin/scripts/common.h
===================================================================
RCS file: /cvsroot/pgsql/src/bin/scripts/common.h,v
retrieving revision 1.19
diff -c -c -r1.19 common.h
*** src/bin/scripts/common.h 1 Jan 2008 19:45:56 -0000 1.19
--- src/bin/scripts/common.h 14 May 2008 15:11:36 -0000
***************
*** 42,45 ****
--- 42,47 ----

extern void setup_cancel_handler(void);

+ extern char *pg_strdup(const char *string);
+
#endif /* COMMON_H */
Index: src/interfaces/ecpg/ecpglib/prepare.c
===================================================================
RCS file: /cvsroot/pgsql/src/interfaces/ecpg/ecpglib/prepare.c,v
retrieving revision 1.27
diff -c -c -r1.27 prepare.c
*** src/interfaces/ecpg/ecpglib/prepare.c 12 May 2008 16:29:04 -0000 1.27
--- src/interfaces/ecpg/ecpglib/prepare.c 14 May 2008 15:11:37 -0000
***************
*** 117,123 ****
struct statement *stmt;
struct prepared_statement *this,
*prev;
- struct sqlca_t *sqlca = ECPGget_sqlca();
PGresult *query;

con = ecpg_get_connection(connection_name);
--- 117,122 ----
A few warnings have crept into CVS HEAD; the attached patch fixes them.

--
Bruce Momjian <bruce@momjian.us>

http://momjian.us

EnterpriseDB

http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

[COMMITTERS] pgsql: Fix a few warnings that have crept into CVS HEAD.

Log Message:
-----------
Fix a few warnings that have crept into CVS HEAD.

Modified Files:
--------------
pgsql/src/bin/scripts:
common.h (r1.19 -> r1.20)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/bin/scripts/common.h?r1=1.19&r2=1.20)
pgsql/src/interfaces/ecpg/ecpglib:
prepare.c (r1.27 -> r1.28)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/ecpglib/prepare.c?r1=1.27&r2=1.28)

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

Re: [BUGS] Problem with TOAST tables when removing TOASTable column

=?iso-8859-2?Q?Wojciech_Strza=B3ka?= <wstrzalka@gmail.com> writes:
> In my opinion the fact that dropping column doesn't release it's toastable resources is a bug.

To make that happen would require (at least) a full table scan. I think
most people are more interested in DROP COLUMN being a cheap operation
than in having the space be reclaimed quickly.

For a comparison point: large field values that don't happen to get
toasted don't vanish immediately, either.

regards, tom lane

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

Re: [PATCHES] libpq object hooks

On Wed, May 14, 2008 at 10:52 AM, Bruce Momjian <bruce@momjian.us> wrote:
> Merlin Moncure wrote:
>> Regarding the other comments:
>> *) API structure: Our major objective was to minimize exports to
>> libpq. I think both copyresult and setvalue have some possible
>> sideband usage (footguns or no). Additional functions could be
>> speculated but are not required by libpqtypes. We would have no
>> problem adding a few things to complete the api if necessary.
>>
>> The patch is basically the minimum libpqtypes needs and has to work
>> more or less as written. We tried a few times to suggest implementing
>> the split a different way (basically, more invasion into libpq). We
>> couldn't get any action there.
>>
>> If the patch is rejected on general merits...that signals the death
>> blow for libpqtypes. We have a chicken/egg problem...people can't use
>> it without patching libpq which will really hamper its adoption, which
>> is, uh, needed to justify the patch. For the record, we have had a
>> couple of dozen downloads of the libpqtypes library on pgfoundry since
>> we put it up there last week. Based on how it has simplified and
>> improved our own code vs. libpq, we have absolutely no doubts it is a
>> major improvement over PQexecParams.
>
> One idea would be to add the libpq hooks but not document them. This
> way, we can modify or remove the API as needed in the future. As
> libpqtypes matures and we are sure what the API should be, we can
> document it as stable and permanent.

The API functions relating to hooks are unlikely to change once
settled on...but PQsetvalue/PQcopyResult are a good fit with your idea
(they introduce new behaviors that could possibly be used outside of
libpqtypes context, and could require changes down the line).

merlin

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

Re: [PATCHES] libpq object hooks

On Wed, May 14, 2008 at 10:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> No, they could revise their patch to be more stylistically in keeping
> with libpq. I haven't looked at the current version of the patch yet,
> but the early versions seemed quite overengineered to me, so your
> criticism didn't surprise me.

I think you'll find the latest version more reasonable. We tried to
keep the over-engineering, so to speak, on our side and make the libpq
changes surgical.

> I'm wondering why the hooks need names at all. AFAICS all that
> libpq needs to know about a hook is a callback function address
> and a void * passthrough pointer.

Here are the proposed API changes [aside: this is one of two breaks
from your initial suggestions..the other being PQcopyResult]:

+ PQcopyResult 142
+ PQsetvalue 143
+ PQresultAlloc 144
+ PQaddObjectHooks 145
+ PQaddGlobalObjectHooks 146
+ PQhookData 147
+ PQresultHookData 148

In question is:
+ void *
+ PQhookData(const PGconn *conn, const char *hookName)

Basically, libpqtypes has various functions that take a PGconn that
need the private data that is stored in libpq with the connection.
PQhookData just does simple linear search and returns the data.

[thinks]
are you suggesting something like
+ void *
+ PQhookData(const PGconn *conn, const void *hookHandle)
?

I would have to take a quick look at the code with Andrew C (he'll be
in in a bit)...but this might be doable.

merlin

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

Re: [ADMIN] translating exception messages of postgresql functions

Julius Tuskenis <julius@nsoft.lt> writes:
> As I've read - postgresql always generates P0001 SQLSTATE code for
> raised exceptions. Have enyone tried to distinguish one exception from
> the other in the application?

You'd have to rely on looking at the error message text.

8.4 will let you specify SQLSTATE in RAISE:
http://developer.postgresql.org/pgdocs/postgres/plpgsql-errors-and-messages.html

regards, tom lane

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

Re: [GENERAL] ranked subqueries vs distinct question

On Wed, May 14, 2008 at 09:35:10AM -0500, Decibel! wrote:

> Someone should probably teach the gnumed folks about schemas, too... ;)

Instead of Why? I should have said And what? I am, of
course, open to insights on that.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
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] rounding problems

On May 13, 2008, at 1:36 PM, Justin wrote:
> Is is limit less no, but what is?

numeric is limitless, unless you specifically bound it. Or you run
out of space...
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Re: [GENERAL] ranked subqueries vs distinct question

On Wed, May 14, 2008 at 09:35:10AM -0500, Decibel! wrote:

> SELECT name, zip, zip='04317' AS zipmatch
> FROM urb LEFT JOIN streets ON (streets.urb_id = urb.urb_id )
> ORDER BY zipmatch DESC, name
> ;

The view dem.v_zip2data (which I erronously left out in my
first post) does just that - it joins streets to urbs
thereby providing urbs with zip codes from the streets
table. It, however, only joins those rows which do have a
zip code. That leaves out those cities which don't. Which
makes me want to UNION on the dem.urb table in the initial
problem.

> Of course you'll need to adjust the table and field names appropriately.
No problem, I can do that. I am not getting the approach
right yet.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

Re: [pgsql-es-ayuda] Ayuda con stored procedures en C

Emiliano Moscato escribió:

> Y lo que debería traernos los rows de la tabla temporal, no hace más que
> decirnos que la tabla está vacía. Si después hacemos una consulta sobre la
> misma tabla, nos muestra los datos insertados correctamente.

Hmm. ¿Está declarada la función como VOLATILE? Eso debería hacerla
tomar un nuevo snapshot después de la inserción, con lo cual el SELECT
debería "ver" esas tuplas. Si hubieras declarado la función como
IMMUTABLE, entonces habría el efecto que indicas.

--
Alvaro Herrera

http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
TIP 7: no olvides aumentar la configuración del "free space map"

Re: [GENERAL] rounding problems

On May 12, 2008, at 10:42 PM, Craig Ringer wrote:
> Personally I'd be tempted to use a `double precision' (float8) for
> things like materials consumption.

Or you could just use an un-bounded numeric...
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Re: [PATCHES] libpq object hooks

Merlin Moncure wrote:
> Regarding the other comments:
> *) API structure: Our major objective was to minimize exports to
> libpq. I think both copyresult and setvalue have some possible
> sideband usage (footguns or no). Additional functions could be
> speculated but are not required by libpqtypes. We would have no
> problem adding a few things to complete the api if necessary.
>
> The patch is basically the minimum libpqtypes needs and has to work
> more or less as written. We tried a few times to suggest implementing
> the split a different way (basically, more invasion into libpq). We
> couldn't get any action there.
>
> If the patch is rejected on general merits...that signals the death
> blow for libpqtypes. We have a chicken/egg problem...people can't use
> it without patching libpq which will really hamper its adoption, which
> is, uh, needed to justify the patch. For the record, we have had a
> couple of dozen downloads of the libpqtypes library on pgfoundry since
> we put it up there last week. Based on how it has simplified and
> improved our own code vs. libpq, we have absolutely no doubts it is a
> major improvement over PQexecParams.

One idea would be to add the libpq hooks but not document them. This
way, we can modify or remove the API as needed in the future. As
libpqtypes matures and we are sure what the API should be, we can
document it as stable and permanent.

--
Bruce Momjian <bruce@momjian.us>

http://momjian.us

EnterpriseDB

http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

Re: [GENERAL] ranked subqueries vs distinct question

On Wed, May 14, 2008 at 09:28:50AM -0500, David McNett wrote:

> I think perhaps you have misunderstood what I was suggesting.
Very well possible.

> If the
> SQL in your original post works, then my suggestion will also work.
Indeed, my initial post had a typo. Here is the last (most complex) query as it should be:

select * from (

select distinct on (name) * from (

select *, 1 as rank from dem.v_zip2data where
name ilike 'Lei%' and
zip = '04317'

union all -- avoid distinctness at this level

select *, 2 as rank from dem.urb where name ilike 'Lei%'

) as inner_union

) as unique_union

order by rank, name;

Note the dem.v_zip2data in the rank 1 subquery which is a
view over those cities which do have known zip codes due to
streets (which have zip codes) linked to them.

> In
> my haste to reply I accidentally omitted the where clause of the query.
No problem, I got that.

> Wouldn't this (full example) work?
>
> SELECT
> name,zip,
> (SELECT zip = '04317') as zipmatch
> FROM
> dem.urb
> WHERE name ilike 'lei%'
> ORDER BY zipmatch DESC, name;
>
> If your code runs, this will too.
That conclusion is correct but my code was wrong ;-)

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

Re: [PATCHES] libpq object hooks

On Tue, May 13, 2008 at 11:52 PM, Bruce Momjian <bruce@momjian.us> wrote:
> My personal opinion is still that I would like to see a more general
> usefulness for these functions before adding them to libpq. The
> complexity of the API just mirrors my gut feeling on this.

There has been a lot of demand for the features that libpqtypes
provides...a quick search of the archives demonstrates this. Here are
a few examples of threads from users asking or even trying to
implement some of the things that libpqtypes helps with or indirectly
solves...I am speaking to your point of usefulness here.

[BUGS] BUG #4053: libpq documentation should express clearly, that
integers are passed in network octet order
[PATCHES] [PATCH] automatic integer conversion
[HACKERS] convert int to bytea
[HACKERS] comunication protocol
[HACKERS] PQescapeBytea* version for parameters
[HACKERS] libpq and Binary Data Formats
[GENERAL] binary representation of date and numeric
[GENERAL] binding 64-bit integer
[HACKERS] Last minute mini-proposal (I know, I know) for PQexecf()
[PERFORM] Low throughput of binary inserts from windows to linux
[GENERAL] Storing images as BYTEA or large objects

merlin

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

Re: [ADMIN] translating exception messages of postgresql functions

Julius Tuskenis wrote:
> Hello,
>
> I'd like to ask how is translation of raised exceptions done in
> postgresql applications?

You want it translated or not translated?

> In Sybase ASA you can specify the error
> number when raising it, but in postgresql theres just exception message.
> As I've read - postgresql always generates P0001 SQLSTATE code for
> raised exceptions. Have enyone tried to distinguish one exception from
> the other in the application?

In Postgres 8.4 you will be able to specify error codes to raised
exceptions in PL/pgSQL.

--
Alvaro Herrera

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

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

[BUGS] Problem with TOAST tables when removing TOASTable column

I've found some strange behavoiur of TOAST'able tables.

1. Lets create table with toastable column

CREATE table toastable (
x int ,
y text
);

2. Check toast size - as the table is empty it's size 0 - OK

SELECT relname, pg_relation_size(oid) FROM pg_class where oid=(select reltoastrelid from pg_class where relkind = 'r' and relname = 'toastable');

3. Insert some large record into toastable

INSERT into toastable values (1, pg_read_file('r.txt', 0, 100000));

4. Again check toast size - it's > 0 now - and it's OK as there are some data

SELECT relname, pg_relation_size(oid) FROM pg_class where oid=(select reltoastrelid from pg_class where relkind = 'r' and relname = 'toastable');

5. Drop the only toastable column

ALTER TABLE toastable DROP COLUMN y;

6. To be sure - vacuum

VACUUM FULL;

7. Check toast size. OH NO - IT"S THE SAME AS IN POINT 4 - WHERE IS MY STORAGE??

SELECT relname, pg_relation_size(oid) FROM pg_class where oid=(select reltoastrelid from pg_class where relkind = 'r' and relname = 'toastable');

8. Make some MVCC noise.

UPDATE toastable SET x=x;

9. And vacuum

VACUUM FULL;

10. Here it is - my storage is back (toast size 0)

SELECT relname, pg_relation_size(oid) FROM pg_class where oid=(select reltoastrelid from pg_class where relkind = 'r' and relname = 'toastable');

In my opinion the fact that dropping column doesn't release it's toastable resources is a bug.
I think it would be good if the toast table would be deleted also in such a case (now I have table with
no toastable columns so I don't need it anymore), but that's not so
important as releasing the free space.

Best regards
Wojtek Strzalka

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

Re: [GENERAL] ranked subqueries vs distinct question

On Wed, May 14, 2008 at 09:35:10AM -0500, Decibel! wrote:

> Someone should probably teach the gnumed folks about schemas, too... ;)

Why ? We use several:

dem - demographics stuff
clin - clinical stuff
gm - gnumed internal stuff
i18n - i18n-related stuff
audit - auditing stuff
blobs - large object (bytea/documents) related stuff
cfg - configuration things
ref - reference material
au/de_de/... locale specific addons

What are your suggestions for improvement ?

Yep, there are a few things left over from the
pre-schema-PostgreSQL era which are getting cleaned up
release by release.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

Re: [PATCHES] libpq object hooks

Andrew Dunstan <andrew@dunslane.net> writes:
> It should be noted that while this feels slightly foreign, it isn't
> hugely invasive, unlike the previous effort - it's only a few hundred
> lines of new code.

> If we reject this, presumably the authors will have no alternative than
> to offer libpqtypes as a patch to libpq.

No, they could revise their patch to be more stylistically in keeping
with libpq. I haven't looked at the current version of the patch yet,
but the early versions seemed quite overengineered to me, so your
criticism didn't surprise me.

>> Keep in mind that the original patch supported a single hook being
>> registered.

> Right, it was more the case insensitive part that bothered me.

I'm wondering why the hooks need names at all. AFAICS all that
libpq needs to know about a hook is a callback function address
and a void * passthrough pointer.

regards, tom lane

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

[BUGS] BUG #4167: When generating UUID using UUID-OSSP module, UUIDs are not unique on Windows

The following bug has been logged online:

Bug reference: 4167
Logged by: nicoanto
Email address: na@mnm-consulting.com
PostgreSQL version: 8.3.1.0
Operating system: Windows
Description: When generating UUID using UUID-OSSP module, UUIDs are
not unique on Windows
Details:

Hi all,

I am using the 8.3.1 version of PostgreSQL. I wrote a simple function on
order to generate UUID values using the UUID-OSSP module
The code of the function is the following one :

CREATE FUNCTION uuidgen() RETURNS CHAR(36) AS $$
BEGIN
RETURN uuid_generate_v4()::CHAR(36);
END;
$$ LANGUAGE 'plpgsql';

When using it in a loop, I have got 2 different behaviors :
* on an Ubuntu platform, the generated UUID values are unique
* on a Windows platform, the generated UUID values are not unique most of
the time.

Do you have any idea on how to fix that bug on a Windows platform ?

Regards,

Nicoanto

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

Re: [GENERAL] rounding problems

On Tue, May 13, 2008 at 02:36:18PM -0400, Justin wrote:
> Double holds 15 places which is the highest value of precision it can
> maintain before rounding occurs.
>
> Is is limit less no, but what is?
>
> Practically speaking taking a vale 0.000,000,000,000,001 aka
> 1 trillionth of anything,

But remember that if you add this value onto a large number and then
take off the large number the result will be zero.

(0.000,000,000,01 + 1,000,000) - 1,000,000 ==> 0
0.000,000,000,01 + (1,000,000 - 1,000,000) ==> 0.000,000,000,01

In general, operations on floating point numbers will increase their
errors.

> i view the problem solved for 98% of problems.

Floating point math is good for most problems, hence why most languages
expose the abstraction.

Sam

--
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] ranked subqueries vs distinct question

On May 14, 2008, at 9:07 AM, Karsten Hilbert wrote:

> That doesn't work, unfortunately, because the urb (cities)
> table doesn't have the zip code. That's stored in a street
> table which foreign keys into the urb table.

SELECT name, zip, zip='04317' AS zipmatch
FROM urb LEFT JOIN streets ON (streets.urb_id = urb.urb_id )
ORDER BY zipmatch DESC, name
;

Of course you'll need to adjust the table and field names appropriately.

Someone should probably teach the gnumed folks about schemas, too... ;)
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Re: [BUGS] BUG #4166: Alter table add column from PgAdminIII

"Mike Gagnon" <mike.gagnon@bellnet.ca> writes:
> I used PGAdmin III to add a character varying column(400) length, not null
> default ''. I get the column displayed in psql when I do a simple query
> like SELECT * from MyTable limit 1;

> When I try to do Update MyTable set NewColumn='something'; I get the error
> saying that the column doesn't exist....

I'm betting you have a case sensitivity problem. Either you created the
column as "NewColumn" with quotes and are trying to reference it without
quotes, or vice versa.

regards, tom lane

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

[ADMIN] translating exception messages of postgresql functions

Hello,

I'd like to ask how is translation of raised exceptions done in
postgresql applications? In Sybase ASA you can specify the error
number when raising it, but in postgresql theres just exception message.
As I've read - postgresql always generates P0001 SQLSTATE code for
raised exceptions. Have enyone tried to distinguish one exception from
the other in the application?

--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050

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

Re: [GENERAL] ranked subqueries vs distinct question

On May 14, 2008, at 9:07 AM, Karsten Hilbert wrote:
> That doesn't work, unfortunately, because the urb (cities)
> table doesn't have the zip code. That's stored in a street
> table which foreign keys into the urb table. The
> dem.v_zip2data view aggregates streets, cities, states and
> countries for which there is a know linkage to a zip code at
> the street level. IOW, there are cities for which there is
> no known zip code. I want those to be matched, too, of
> course, courtesy of the user typing part of their name.

I think perhaps you have misunderstood what I was suggesting. If the
SQL in your original post works, then my suggestion will also work.
In my haste to reply I accidentally omitted the where clause of the
query.

Wouldn't this (full example) work?

SELECT
name,zip,
(SELECT zip = '04317') as zipmatch
FROM
dem.urb
WHERE name ilike 'lei%'
ORDER BY zipmatch DESC, name;

If your code runs, this will too.

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

Re: [pgsql-es-ayuda] tipo de dato autoincremental

On Wed, May 14, 2008 at 9:37 AM, william diaz <widipa@gmail.com> wrote:
>
> cual es la direfencia entre esos tipos de datos y colocarle a un campo una
> secuencia (nextval('secuencia')) ?

SERIAL y BIGSERIAL no son 'verdaderos' tipos de datos, sino son
convenciones para representar columnas con identificador único. Si
haces esto:

CREATE TABLE tabla (
columna SERIAL
);

equivale a que hagas:

CREATE SEQUENCE tabla_columna_seq;
CREATE TABLE tabla (
columna integer NOT NULL DEFAULT nextval('tabla_columna_seq')
);
ALTER SEQUENCE tabla_columna_seq OWNED BY tabla.columna;

Depende de vos cual utilizar, aunque utilizar SERIAL y BIGSERIAL te
simplifica el proceso!

Dale una leida a:

http://www.postgresql.org/docs/current/interactive/datatype-numeric.html#DATATYPE-SERIAL

--
Saludos y abrazos...

Marco Antonio Frias Butrón
Slackware Linux User
Linux Registered User #356229 - http://counter.li.org/
--
TIP 3: Si encontraste la respuesta a tu problema, publ�cala, otros te lo agradecer�n

Re: [pgsql-es-ayuda] Ayuda con stored procedures en C

Hola de nuevo!!

Luego del impulso que nos dio Alvaro con la documentacion de SPI avanzamos un monton. Estamos manejando cursores, estamos haciendo las consultas, mergeando tablas antes de devolverlas, etc. Peeeero...
Estamos teniendo un nuevo problemita:
  Desde un stored procedure estamos haciendo secuencialmente lo siguiente:
  - Creamos una tabla temporal.
  - Insertamos en la tabla temporal rows que sacamos de otra (usando un stored procedure).
  - Después leemos dicha tabla temporal para mostrar los rows en pantalla.

  Y lo que debería traernos los rows de la tabla temporal, no hace más que decirnos que la tabla está vacía. Si después hacemos una consulta sobre la misma tabla, nos muestra los datos insertados correctamente.
  Nos da la sensación de que el select final se está ejecutando antes que el insert, a pesar de ser llamados secuencialmente. Llegamos a esta conclusión porque también probamos de usar una tabla no temporal donde insertar los datos, y nos mostraba la información que había antes de la inserción. También probamos de hacer un begin y un comit encerrando los primeros dos pasos, pero no cambió el problema.
Revisando en la documentación de SPI encontramos la siguiente línea en el detalle de spi_execute:

It is generally unwise to mix read-only and read-write commands within a single function using SPI; that could result in very confusing behavior, since the read-only queries would not see the results of any database updates done by the read-write queries.

Pero nosotros necesitaríamos hacer esto mismo que está desaconsejado :'(
Alguna idea?
Gracias totales.
Saldudos cordiales

Emiliano

El día 25 de abril de 2008 16:37, Emiliano Moscato <moski666@gmail.com> escribió:

En el fragor del trabajo se me había olvidado agradecerte :)
Me fueron muy utiles los links, no lo había visto en la documentación de Postgres porque está separado de lo que es Stored Procedures en C (Cap 39 creo recordar).
Gracias, los seguiré molestando en breve :)
Saludos

Emiliano

2008/4/24 Alvaro Herrera <alvherre@commandprompt.com>:

Emiliano Moscato escribió:

> Hola,
>
> Estoy empezando a hacer unas cositas con stored procedures escritos en C. Si
> bien voy a hacer algunas cosas complejas, estoy tratando de entender como
> usarlo. Los ejemplos que llevo encontrados hacen o un sencillo "hello world"
> o "add_one" o van directamente a un "crosstab" que hace cosas demasiado
> complicadas para el nivel en el que estoy hoy por hoy, lo que hace q me
> cueste bastante entender como se hace algo relativamente simple: una función
> "query()" que reciba un string, lo ejecute asumiendo que es un query SQL y
> devuelva el resultado.

Mira la documentacion de SPI_exec aca:

http://www.postgresql.org/docs/8.3/static/spi-spi-exec.html

Aca hay un ejemplo

http://www.postgresql.org/docs/8.3/static/spi-examples.html

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda

--
mOsKi
"No hay nada que uno haga mal , lo que hay es poco vino." Autor Anonimo

--
mOsKi
"No hay nada que uno haga mal , lo que hay es poco vino." Autor Anonimo

Re: [PATCHES] libpq object hooks

On Wed, May 14, 2008 at 8:18 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
> Right, it was more the case insensitive part that bothered me.
Done. We in fact had realized this was a mistake anyways following
some profiling of the libpqtypes library. In some scenarios, this
function gets called a lot.

Regarding the other comments:
*) API structure: Our major objective was to minimize exports to
libpq. I think both copyresult and setvalue have some possible
sideband usage (footguns or no). Additional functions could be
speculated but are not required by libpqtypes. We would have no
problem adding a few things to complete the api if necessary.

The patch is basically the minimum libpqtypes needs and has to work
more or less as written. We tried a few times to suggest implementing
the split a different way (basically, more invasion into libpq). We
couldn't get any action there.

If the patch is rejected on general merits...that signals the death
blow for libpqtypes. We have a chicken/egg problem...people can't use
it without patching libpq which will really hamper its adoption, which
is, uh, needed to justify the patch. For the record, we have had a
couple of dozen downloads of the libpqtypes library on pgfoundry since
we put it up there last week. Based on how it has simplified and
improved our own code vs. libpq, we have absolutely no doubts it is a
major improvement over PQexecParams.

merlin

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

Re: [HACKERS] [PATCHES] stored procedure stats in collector

[ redirecting to pghackers for wider discussion ]

Martin Pihlak <martin.pihlak@gmail.com> writes:
>> What I think we should do about that is forget tracking getrusage()'s
>> user/system/real time and just track elapsed time.

> I find the utime/stime quite useful, compared with the actual time it
> enables us to distinguish waiters (remote calls, sleeps, etc) from the
> actual CPU hogs.

Well, what it is going to cost us to have that is double the space
in the pgstats file (64 bytes per function instead of 32) --- and that
isn't a choice we can flip with a GUC. This is a hot button for a
lot of people because we know that bloat in the pgstats file translates
directly to continual I/O overhead. (Perhaps that'll be fixed by the
time this patch hits production, but I'm not holding my breath.)

The runtime overhead is pretty daunting also. It's not just twice as
many kernel calls, it's which ones you are making. On a lot of modern
machines gettimeofday() is optimized to not enter the kernel at all,
while getrusage() will hardly be. [ click click, test test ] On my
x86_64 Fedora 8 machine, it appears that gettimeofday() requires about
60 nsec per call, whereas getrusage(RUSAGE_SELF) requires 788 nsec.

One other point here is that accuracy of the results is questionable.
On Windows we will certainly find that gettimeofday is useless and
we need to use QueryPerformanceCounter instead (see the code in
instrument.h/.c). I wonder what the accuracy of GetProcessTimes
is and whether it will even deliver answers consistent with
QueryPerformanceCounter. On Unix-ish machines the corresponding
worry is that getrusage results might be tracked only to the clock
tick and not any finer grain.

Double the pgstats storage and a dozen times as much runtime overhead
in exchange for questionable numbers is a pretty hard sell. I remain
of the opinion that we should just track elapsed time.

regards, tom lane

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

Re: [GENERAL] ranked subqueries vs distinct question

On Wed, May 14, 2008 at 08:43:31AM -0500, David McNett wrote:

>> Effectively I want known-zip cities first, then
>> fragment-matching cities but without those already in the
>> known-zip list.
>
> I think you've made things far more complicated than you need.
Very likely, yes.

> How about an approach something along these lines...
>
> SELECT
> name,zip,
> (SELECT zip = '04317') as zipmatch
> FROM
> dem.urb
> ORDER BY zipmatch DESC, name;

That doesn't work, unfortunately, because the urb (cities)
table doesn't have the zip code. That's stored in a street
table which foreign keys into the urb table. The
dem.v_zip2data view aggregates streets, cities, states and
countries for which there is a know linkage to a zip code at
the street level. IOW, there are cities for which there is
no known zip code. I want those to be matched, too, of
course, courtesy of the user typing part of their name.

> No unions, no messy intersection problems. Much faster. I hope that
> applies to your situation.
Not quite, unfortunately.

The full schema can be seen here:

http://salaam.homeunix.com/~ncq/gnumed/schema/devel/gnumed-schema.html

Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

[COMMITTERS] pgsql: Remove the special variable for open_sync_bit used in O_SYNC and

Log Message:
-----------
Remove the special variable for open_sync_bit used in O_SYNC and O_DSYNC
modes, replacing it with a call to a function that derives it from the
sync_method variable, now that it has distinct values for these two cases.

This means that assign_xlog_sync_method() no longer changes any settings,
thus fixing the bug introduced in the change to use a guc enum for
wal_sync_method.

Modified Files:
--------------
pgsql/src/backend/access/transam:
xlog.c (r1.308 -> r1.309)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/transam/xlog.c?r1=1.308&r2=1.309)

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

Re: [GENERAL] ranked subqueries vs distinct question

On Wed, May 14, 2008 at 09:48:20PM +0800, mian wang wrote:

> select * from (
> select *, 1 as rank from dem.urb where
> name ilike 'Lei%' and
> zip = '04317'
> union -- avoid distinctness at this level
> select *, 2 as rank from dem.urb where name ilike 'Lei%'
> )
> order by rank, name;

This surely avoids duplicates but it doesn't guarantuee rank
1 matches are on top because the distinct happens *before*
the order by and thus the "surviving" rank value is AFAICT
unpredictably 1 OR 2. I want rank 1 on top and rank 2
duplicates discarded.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

Re: [HACKERS] Syntax decisions for pl/pgsql RAISE extension

"Zeugswetter Andreas OSB sIT" <Andreas.Zeugswetter@s-itsolutions.at> writes:
> Other db's go with SQLCODE and SQLSTATE.
> Would SQLCODE be better than ERRCODE ?

No, because SQLCODE has a specific meaning, and it's *not* either a
condition name or a SQLSTATE --- it's the old SQL89-era error code
numbering. I think this would just create confusion.

regards, tom lane

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

Re: [HACKERS] Execution Plan Cost

The read-only plan of the query (SELECT $1 > 5) is prepared, so there is
not parsing or planning. Any insight into what operations account for the
executor startup/shutdown time?

Thanks a lot,

Luis Vargas

On May 8 2008, Tom Lane wrote:

>Luis Vargas <Luis.Vargas@cl.cam.ac.uk> writes:
>> At the backend, I'm measuring the cost of executing (via
>> SPI_execute_plan) the read-only plan of a simple query with no reference
>> to tables. E.g. simpleplan(int) AS SELECT $1 > 5
>
>> Executing this plan via SPI_execute takes around 70% more time than
>> directly executing the relevant operator function (int4gt) and using
>> DatumGetBool.
>
>Only that much? I'd have expected it to be several hundred times
>slower, considering that int4gt is an utterly trivial function and
>executor startup/shutdown is a fairly heavyweight operation.
>
> regards, tom lane
>

--
**************************************************************************
PhD Research Student
Room FE04, Computer Laboratory, University of Cambridge
Office: +44 (0) 1223 763 776 Mobile: +44 (0) 7767 086 105
MSN Messenger: luis_herring@hotmail.com
**************************************************************************

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

Re: [GENERAL] ranked subqueries vs distinct question

2008/5/14 Karsten Hilbert <Karsten.Hilbert@gmx.net>:

Hi all,

let's assume I want to select cities by name fragment:

       select * from dem.urb where name ilike 'Lei%';

Then, let's assume I know the zip code and want to use that
for limiting the range of cities returned:

       select * from dem.urb where
               name ilike 'Lei%' and
               zip = '04317'
       ;

Now, let's assume I have a widget which suggests cities
based on the typed fragment. Another widget will already got
the zip code and has it communicated to the city search
field. So I want to suggest a list of cities which a) have
the fragment and the zip code  and b) have the fragment. But
the user may have entered the wrong zip code, so include the
cities which have just the fragment, too:

       select * from (

               select * from dem.urb where
                       name ilike 'Lei%' and
                       zip = '04317'

               union all               -- avoid distinctness at this level

               select * from dem.urb where name ilike 'Lei%'

       );

However, I want those ordered by name:

       select * from (

               select * from dem.urb where
                       name ilike 'Lei%' and
                       zip = '04317'

               union all               -- avoid distinctness at this level

               select * from dem.urb where name ilike 'Lei%'

       )
       order by name;

Then, I also want the ones with the zip code listed at the
top of the list because they are more likely to be the ones
(after all we already have the zip code !):

       select * from (

               select *, 1 as rank from dem.urb where
                       name ilike 'Lei%' and
                       zip = '04317'

               union all               -- avoid distinctness at this level

               select *, 2 as rank from dem.urb where name ilike 'Lei%'

       )
       order by rank, name;

This is fine. One nuisance remains: Cities which match both
zip and name are (of course) listed twice. To eliminate
duplicates:

       select distinct on (name) * from (

               select *, 1 as rank from dem.urb where
                       name ilike 'Lei%' and
                       zip = '04317'

               union all               -- avoid distinctness at this level

               select *, 2 as rank from dem.urb where name ilike 'Lei%'

       ) as inner_union

       order by rank, name;

This sayeth (as it should):

       ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Modifying to:

       select * from (

               select distinct on (name) * from (

                       select *, 1 as rank from dem.urb where
                               name ilike 'Lei%' and
                               zip = '04317'

                       union all               -- avoid distinctness at this level

                       select *, 2 as rank from dem.urb where name ilike 'Lei%'

               ) as inner_union

       ) as unique_union

       order by rank, name;

This works. However, one nuisance remains: Because the
distinct happens before the order by rank it is happenstance
whether rank 1 cities (with zip) will be listed on top
anymore.

Effectively I want known-zip cities first, then
fragment-matching cities but without those already in the
known-zip list.

Can anyone teach me how I need to do this in SQL ?

Do I really have to explicitely EXCEPT out the first list
from the second sub query in the union ?

Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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

 

select * from (

               select *, 1 as rank from dem.urb where
                       name ilike 'Lei%' and
                       zip = '04317'

               union               -- avoid distinctness at this level

               select *, 2 as rank from dem.urb where name ilike 'Lei%'

       )
       order by rank, name;

--
Kind Regards,
Mian

Re: [GENERAL] ranked subqueries vs distinct question

On May 14, 2008, at 8:15 AM, Karsten Hilbert wrote:
> Effectively I want known-zip cities first, then
> fragment-matching cities but without those already in the
> known-zip list.
>
> Can anyone teach me how I need to do this in SQL ?

I think you've made things far more complicated than you need. How
about an approach something along these lines...

SELECT
name,zip,
(SELECT zip = '04317') as zipmatch
FROM
dem.urb
ORDER BY zipmatch DESC, name;

This will give you a nice resultset incorporating a boolean field ('t'
or 'f') reflecting whether or not the zip code matches. Sorting DESC
on that "zipmatch" field will put the trues at the top of your result
set.

No unions, no messy intersection problems. Much faster. I hope that
applies to your situation.

-Nugget

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

Re: [pgsql-es-ayuda] tipo de dato autoincremental

2008/5/8 Diego Gil <listas@adminsa.com>:

El jue, 08-05-2008 a las 14:19 -0400, Sonia Perez Peña escribió:

> Hola amigos, existe en PostgresSQL autoincremental, si existen podían
> indicarme cual es.
>
>
>
> Gracias,
>
>

serial y bigserial.

cual es la direfencia entre esos tipos de datos y colocarle a un campo una secuencia (nextval('secuencia')) ?
 --
Cordialmente.


William Diaz Pabón
Ing. de Sistemas
Soluciones Empresariales.
Cel: 3173314172 - 3156396316
Tunja - Boyacá - Colombia

Re: [pgsql-es-ayuda] Problemas al arrancar postgres como usuario 'postgres'

El día 9 de mayo de 2008 6:23, Moises Alberto Lindo Gutarra <mlindo@gmail.com> escribió:

tu primer problema se debe seguramente a que la cuenta 'postgres' este bloqueda
o en su defecto a caducado el password.

El día 9 de mayo de 2008 6:09, Gabriela Messner <gabmessner@gmail.com> escribió:

> Hola, tengo instalado un PostgreSQL 8.2 sobre Win XP con una cuenta de
> usuario 'postgres' asociada. Como es una máquina de escritorio al finalizar
> la jornada la apago. El problema surge cuando tras varios días arrancando la
> máquina, en uno de los arranques el servicio PostgreSQL Database Server no
> se inicia y al intentar iniciarlo como usuario 'postgres' me devuelve un
> error y la única opción que me queda es arrancarlo como cuenta del sistema
> local. ¿Cómo puedo corregir esto para que se incie sesión correctamente como
> 'postgres'?.
>
> Otra problema que tengo es al crear una tabla y poblarla posteriormente con
> el comando COPY. Una vez la tabla está poblada la abro con 'PGAdmin III Edit
> Data' pero no me permite editar manualmente los registros. Este problema
> sólo lo detecto con el comando COPY; cuando pueblo la tabla con el comando
> shp2pgsql (extensión Postgis) puedo editar los registros de forma correcta.
>
>

Alguna vez me paso lo mismo y era porque la tabla no tenia llave primaria

--
Cordialmente.


William Diaz Pabón
Ing. de Sistemas
Soluciones Empresariales.
Cel: 3173314172 - 3156396316
Tunja - Boyacá - Colombia

[GENERAL] ranked subqueries vs distinct question

Hi all,

let's assume I want to select cities by name fragment:

select * from dem.urb where name ilike 'Lei%';

Then, let's assume I know the zip code and want to use that
for limiting the range of cities returned:

select * from dem.urb where
name ilike 'Lei%' and
zip = '04317'
;

Now, let's assume I have a widget which suggests cities
based on the typed fragment. Another widget will already got
the zip code and has it communicated to the city search
field. So I want to suggest a list of cities which a) have
the fragment and the zip code and b) have the fragment. But
the user may have entered the wrong zip code, so include the
cities which have just the fragment, too:

select * from (

select * from dem.urb where
name ilike 'Lei%' and
zip = '04317'

union all -- avoid distinctness at this level

select * from dem.urb where name ilike 'Lei%'

);

However, I want those ordered by name:

select * from (

select * from dem.urb where
name ilike 'Lei%' and
zip = '04317'

union all -- avoid distinctness at this level

select * from dem.urb where name ilike 'Lei%'

)
order by name;

Then, I also want the ones with the zip code listed at the
top of the list because they are more likely to be the ones
(after all we already have the zip code !):

select * from (

select *, 1 as rank from dem.urb where
name ilike 'Lei%' and
zip = '04317'

union all -- avoid distinctness at this level

select *, 2 as rank from dem.urb where name ilike 'Lei%'

)
order by rank, name;

This is fine. One nuisance remains: Cities which match both
zip and name are (of course) listed twice. To eliminate
duplicates:

select distinct on (name) * from (

select *, 1 as rank from dem.urb where
name ilike 'Lei%' and
zip = '04317'

union all -- avoid distinctness at this level

select *, 2 as rank from dem.urb where name ilike 'Lei%'

) as inner_union

order by rank, name;

This sayeth (as it should):

ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Modifying to:

select * from (

select distinct on (name) * from (

select *, 1 as rank from dem.urb where
name ilike 'Lei%' and
zip = '04317'

union all -- avoid distinctness at this level

select *, 2 as rank from dem.urb where name ilike 'Lei%'

) as inner_union

) as unique_union

order by rank, name;

This works. However, one nuisance remains: Because the
distinct happens before the order by rank it is happenstance
whether rank 1 cities (with zip) will be listed on top
anymore.

Effectively I want known-zip cities first, then
fragment-matching cities but without those already in the
known-zip list.

Can anyone teach me how I need to do this in SQL ?

Do I really have to explicitely EXCEPT out the first list
from the second sub query in the union ?

Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

[COMMITTERS] plproxy - plproxy: Better poll() compat and few cleanups.

Log Message:
-----------
Better poll() compat and few cleanups.

- Make resulting fd-count correct for poll().
- Don't allow any negative value as timeout, only -1.
- Clearer label names.

Modified Files:
--------------
plproxy/src:
poll_compat.c (r1.1 -> r1.2)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/plproxy/plproxy/src/poll_compat.c.diff?r1=1.1&r2=1.2)

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

[COMMITTERS] plproxy - plproxy: make RUN optional, defaulting to RUN ON 0;

Log Message:
-----------
make RUN optional, defaulting to RUN ON 0;

Modified Files:
--------------
plproxy/src:
parser.y (r1.6 -> r1.7)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/plproxy/plproxy/src/parser.y.diff?r1=1.6&r2=1.7)
plproxy/sql:
plproxy_test.sql (r1.4 -> r1.5)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/plproxy/plproxy/sql/plproxy_test.sql.diff?r1=1.4&r2=1.5)
plproxy/expected:
plproxy_test.out (r1.4 -> r1.5)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/plproxy/plproxy/expected/plproxy_test.out.diff?r1=1.4&r2=1.5)

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

Re: [PATCHES] stored procedure stats in collector

> What I think we should do about that is forget tracking getrusage()'s
> user/system/real time and just track elapsed time. We have the
> technology to get that in a portable fashion (cf the well-proven
> instrument.c code). Such a decision would also alleviate two of the
> biggest negatives of this patch, which are the runtime overhead and
> the extent to which it's going to bloat the pgstats file.
>

I find the utime/stime quite useful, compared with the actual time it
enables us to distinguish waiters (remote calls, sleeps, etc) from the
actual CPU hogs. The difference is also very visible for IO bound
functions. At least in our case it is a very useful tool for diagnosing
performance issues and the overhead is not really visible.

Perhaps the track_functions should just be set to none, or enabled selectively
(session, function guc, user guc) for the environments where getrusage()
is particularly expensive? Maybe a note in the docs that tracking is
potentially expensive, and should be used carefully in production env.

Regards,
Martin

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

Re: [PATCHES] libpq object hooks

Alvaro Herrera wrote:
> Andrew Dunstan escribió:
>
>
>> The thing that is a bit disturbing is that the whole style of this
>> scheme is very different from the fairly simple APIs that the rest of
>> libpq presents. It's going to make libpq look rather odd, I think. I
>> would have felt happier if the authors had been able to come up with a
>> simple scheme to add API calls to export whatever information they
>> needed, rather than using this callback scheme.
>>
>
> I'm not sure I understand this point. Remember that this is here to
> support the libpqtypes library. There doesn't seem to be a way for an
> API such as you describe to work.
>

That might well be true. The issue then becomes "Do we want to add
something with this flavor to libpq?" I take it Bruce's answer is "No",
at least until he has seen more evidence of general usefulness. I think
we need to make a decision on this before anyone wastes any more time.

It should be noted that while this feels slightly foreign, it isn't
hugely invasive, unlike the previous effort - it's only a few hundred
lines of new code.

If we reject this, presumably the authors will have no alternative than
to offer libpqtypes as a patch to libpq. ISTM that we're then asking
them to climb over a fairly high hurdle. On the one hand we want them to
demonstrate that there's demand for their tool and on the other we make
it difficult to distribute and deploy.

>
>> Second, the hook names are compared case insensitively and by linear
>> search. I don't see any justification for using case insensitive names
>> for hooks in a C program, so I think that part should go. And if we
>> expect to keep anything other than trivial numbers of hooks we should
>> look at some sort of binary or hashed search.
>>
>
> Keep in mind that the original patch supported a single hook being
> registered. Perhaps we could dream about having a couple of hooks
> registered, but turning into hashed search would seem to be overkill, at
> least for now. (If hooking into libpq is truly successful we can always
> improve it later -- it's not an exported detail of the API after all.)
>
>

Right, it was more the case insensitive part that bothered me.

cheers

andrew

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

[COMMITTERS] plproxy - plproxy: Reset cluster_sql on parser entry.

Log Message:
-----------
Reset cluster_sql on parser entry.

Fixes crash in case CLUSTER 'name'; follows CLUSTER func();

Modified Files:
--------------
plproxy/src:
parser.y (r1.5 -> r1.6)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/plproxy/plproxy/src/parser.y.diff?r1=1.5&r2=1.6)

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

Re: [pgsql-es-ayuda] charla sobre postgres

Omar:

Estamos proponiendo que Alvaro y Jaime lo impartan ... cuando ellos
organicen su agenda se harán propuestas para todos.

Pero sigue por ahora mirando los link que te dio Ernesto ... esperamos
que el curso sea especie de un taller donde todos aportemos lo que
podamos.

Gilberto.

El mar, 13-05-2008 a las 17:23 -0500, Omar Alfredo De la hoz Maestre
escribió:
>
> ok....
>
>
> gracias ernesto...iniciare con esto que me has enviado...
>
> gilberto claro que si seria uno mas...pero podrias explicarme com ose
> dara el curso en que consite....
>
> gracias
>
>
>
>
>
> Omar De la hoz Maestre
> Ing. de Sistemas
> Santa Marta
> Colombia
>
>

--
TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda

Re: [pgsql-de-allgemein] Error connecting to the server: Server beendete die Verbindung unerwartet

am Wed, dem 14.05.2008, um 13:38:16 +0200 mailte Exner, Peter folgendes:
> Danke für die schnelle Reaktion!

Bitte.

> - Bis letzte Woche lief alles anstandslos, und zwar schon lange. Irgendwelche
> relevanten Änderungen am System habe ich nicht vorgenommen, jedenfalls nicht
> bewusst.
>
> Kannst du mit diesen Infos etwas anfangen?

Nicht wirklich. Steht denn was im Logfile? Ich vermute ja irgend etwas
in Richtung Tischbrandschutzmauer.

Was passiert bei 'telnet localhost 5432'?

>
> Viele Grüße
>
> Peter
>
>
>
> > -----Ursprüngliche Nachricht-----

Bitte kein TOFU, ich stelle mich zum Maillesen nicht auf den Kopf.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA

http://wwwkeys.de.pgp.net

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

Re: [pgsql-de-allgemein] Error connecting to the server: Server beendete die Verbindung unerwartet

Danke für die schnelle Reaktion!

- Der Server, der beim Hochfahren des Rechners automatisch startet, startet anscheinend normal, lässt sich beenden und wieder neu starten (über Start - Programme - Dienst starten bzw. beenden).

- Im cmd antwortet psql mit derselben Meldung wie unten: "Server beendete die Verbindung unerwartet Das heißt wahrscheinlich, daß der Server abnormal beendete bevor oder während die Anweisung bearbeitet wurde". Das geht also auch nicht.

- Aufgefallen ist es mir beim Verbindungsversuch.

- Bis letzte Woche lief alles anstandslos, und zwar schon lange. Irgendwelche relevanten Änderungen am System habe ich nicht vorgenommen, jedenfalls nicht bewusst.

Kannst du mit diesen Infos etwas anfangen?

Viele Grüße

Peter

 

> -----Ursprüngliche Nachricht-----

> Von: pgsql-de-allgemein-owner@postgresql.org

> [mailto:pgsql-de-allgemein-owner@postgresql.org] Im Auftrag von A.

> Kretschmer

> Gesendet: Mittwoch, 14. Mai 2008 11:20

> An: pgsql-de-allgemein@postgresql.org

> Betreff: Re: [pgsql-de-allgemein] Error connecting to the

> server: Server beendete die Verbindung unerwartet

>

> am Wed, dem 14.05.2008, um 10:42:02 +0200 mailte Exner, Peter

> folgendes:

> > Hallo,

> >

> > beim Verbindungsversuch mit meinem Postgres-Server 8.1.3

> auf localhost

> > (Windows) erscheint jetzt immer die Meldung

> >

> > ---------------------------

> > pgAdmin III

> > ---------------------------

> > Ein Fehler ist aufgetreten:

> >

> > Error connecting to the server: Server beendete die Verbindung

> > unerwartet Das heißt wahrscheinlich, daß der Server

> abnormal beendete

> > bevor oder während die Anweisung bearbeitet wurde.

> >

>

> - Läuft denn der Server noch?

> - trat es mitten im Betrieb auf oder gleich beim Verbindungsversuch?

> - sind Tischfeuerwände und ähnlich nutzlose Software mit installiert?

> - kannst Du Dich via psql verbinden?

>

>

> Andreas

> --

> Andreas Kretschmer

> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)

> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

>

> --

> Sent via pgsql-de-allgemein mailing list

> (pgsql-de-allgemein@postgresql.org)

> To make changes to your subscription:

> http://www.postgresql.org/mailpref/pgsql-de-allgemein

>

[HACKERS] how to perform silent installation on linux and solaris

Hi All

 

I have been trying to perform silent installation of postgres on linux and solaris …..please help me with this

 

Thanks and Regards

Ranjeet Singh