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
> 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
--
Bruce Momjian <bruce@momjian.us>
EnterpriseDB
+ If your life is a hard drive, Christ can be your backup. +
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
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
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
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
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
> 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
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
> 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
> 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"
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
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>
EnterpriseDB
+ 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
> 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
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
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
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
> 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
> 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
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
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
> 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
> 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
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
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
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
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
Emiliano2008/4/24 Alvaro Herrera <alvherre@commandprompt.com>:Emiliano Moscato escribió:
> Hola,Mira la documentacion de SPI_exec aca:
>
> 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.
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
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
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
>> 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
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
> 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
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
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
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
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
El jue, 08-05-2008 a las 14:19 -0400, Sonia Perez Peña escribió:
> Hola amigos, existe en PostgresSQL autoincremental, si existen podÃanserial y bigserial.
> indicarme cual es.
>
>
>
> Gracias,
>
>
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.
>
>
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
- 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
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
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
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
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
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
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
--
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
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>
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
