[GENERAL] REVOKE ... FROM everybody?
Heyho! As far as I understand, REVOKE .. FROM PUBLIC revokes rights that were given to everybody. Is there a way to revoke rights given to *any*body? (all users)? Or do I misunderstand PUBLIC? cheers -- vbi -- featured product: ClamAV Antivirus - http://www.clamav.net/ signature.asc Description: This is a digitally signed message part.
[GENERAL] Unable to call functions defined in XML2 contrib
Hi All,
I am running PostgreSQL 8.4 offered with OpenSUSE 11.2. It works
exceptionally well. I have installed the contrib package in order to use
the functions provided by XML2/pg_xml as explained in this page:
http://www.postgresql.org/docs/8.4/static/xml2.html
However, when I try to run this SQL command:
SELECT * FROM
xpath_table('id','xml','test',
'/doc/@num|/doc/line/@num|/doc/line/a|/doc/line/b|/doc/line/c',
'true')
AS t(id int4, doc_num varchar(10), line_num varchar(10), val1 int4, val2
int4, val3 int4)
WHERE id = 1 ORDER BY doc_num, line_num
It returns an error saying that:
ERROR: function xpath_table(unknown, unknown, unknown, unknown,
unknown) does not exist
LINE 2: xpath_table('id','xml','test',
^
HINT: *No function matches the given name and argument types. You might
need to add explicit type casts.*
** Error **
ERROR: function xpath_table(unknown, unknown, unknown, unknown, unknown)
does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might
need to add explicit type casts.
Character: 21
Don't know what is wrong.
I am anxiously waiting for your reply.
Franclin.
[GENERAL] beginning and ending of sessions
Hi In the postgres documentation under 1.2. Architectural Fundamentals it is said that a session consists of a server process and a client application. But i don't really understand when a session begins and when it ends. Does a session correspond to a connection such as when calling the php-function pg_connect() ? Thank You jody -- 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] REVOKE ... FROM everybody?
Adrian von Bidder wrote: Heyho! As far as I understand, REVOKE .. FROM PUBLIC revokes rights that were given to everybody. Is there a way to revoke rights given to *any*body? (all users)? Or do I misunderstand PUBLIC? You're misunderstanding -- if you revoke from public, only the grant given to public is revoked. Grants to other roles are kept. To revoke all grants, you need to list them individually IIRC. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres GIS Question
We are getting the data by invoking a webservice in which I found these strange characters. we have a job that takes the data from XML and dumps into our tables. What I am guessing is, it should be fixed at the source. If that's not possible, I might have to take it some how? Regards On Tue, Mar 9, 2010 at 4:53 PM, Jeff Davis pg...@j-davis.com wrote: On Tue, 2010-03-09 at 16:27 -0500, akp geek wrote: we have are using postgis. I am running into this scenarios. When the query is executed it is displaying information as 780/24 520 (500- ? ) as opposed to 780/24 520 (500- 1/4 ) . It's doing in many part. Has any one run into this kind of issue? Is it related to the encoding by any chance? Can you please share your thoughts? You haven't really provided enough information to begin. What is the server_encoding? What is the client_encoding? How are you putting the data in? What kind of special characters are you using, and what do you expect the result to be? Regards, Jeff Davis
Re: [GENERAL] beginning and ending of sessions
Le 12/03/2010 16:00, jody a écrit : [...] In the postgres documentation under 1.2. Architectural Fundamentals it is said that a session consists of a server process and a client application. But i don't really understand when a session begins and when it ends. Does a session correspond to a connection such as when calling the php-function pg_connect() ? Right. And it ends when you call pg_close(). -- Guillaume. http://www.postgresqlfr.org http://dalibo.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: [GENERAL] Unable to call functions defined in XML2 contrib
Franclin Foping f.fop...@4c.ucc.ie writes: I am running PostgreSQL 8.4 offered with OpenSUSE 11.2. It works exceptionally well. I have installed the contrib package in order to use the functions provided by XML2/pg_xml as explained in this page: http://www.postgresql.org/docs/8.4/static/xml2.html [ but it's not there ] Did you actually install the module? This involves both making the pgxml.so file available, and running the pgxml.sql script in each database you want to use the functions in. See http://www.postgresql.org/docs/8.4/static/contrib.html for a general description of installing contrib modules. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Function with DEFAULT arguments
Hello,
I'm trying to use the DEFAULT option to pass parameters to the arguments of a
function.
When I call that function, how can I change the default value of some
arguments and leave as default the value of other arguments? In other words, is
there a way to 'call' the arguments by their names so to specify which should
have their default value changed?
Here's a toy example, a function that takes three strings as arguments and
concatenate them:
CREATE FUNCTION test_default(string1 text default 'a', string2 text default
'b', string3 text default 'c') RETURNS text AS $$
BEGIN
RETURN string1 || string2 || string3;
END;
$$ language 'plpgsql';
-- Only default args:
SELECT test_default(); -- abc
-- With custom values:
SELECT test_default('X', 'Y', 'Z'); -- XYZ
-- Now, how can I leave as default the 1st and 3rd argument (string1 and
string3) and change only the second one (string2)? I would like to do something
like:
SELECT test_default(string2= 'Y'); -- To return 'aYb'
And in general, are there any examples/documentation that show how to use the
option default?
I'm using postgresql 8.4.2 on Windows XP.
Many thanks
Dario
--
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] Function with DEFAULT arguments
On Fri, Mar 12, 2010 at 05:28:57PM +0100, dario@libero.it wrote:
Hello,
I'm trying to use the DEFAULT option to pass parameters to the arguments of a
function.
When I call that function, how can I change the default value of some
arguments and leave as default the value of other arguments? In other words,
is
there a way to 'call' the arguments by their names so to specify which should
have their default value changed?
Here's a toy example, a function that takes three strings as arguments and
concatenate them:
CREATE FUNCTION test_default(string1 text default 'a', string2 text default
'b', string3 text default 'c') RETURNS text AS $$
BEGIN
RETURN string1 || string2 || string3;
END;
$$ language 'plpgsql';
-- Only default args:
SELECT test_default(); -- abc
-- With custom values:
SELECT test_default('X', 'Y', 'Z'); -- XYZ
-- Now, how can I leave as default the 1st and 3rd argument (string1 and
string3) and change only the second one (string2)? I would like to do
something
like:
You can't unless you're on PostgreSQL 9.0:
http://www.depesz.com/index.php/2009/11/17/waiting-for-8-5-named-function-arguments/
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
--
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] Unable to call functions defined in XML2 contrib
On 12/03/10 15:34, Tom Lane wrote: Franclin Foping f.fop...@4c.ucc.ie writes: I am running PostgreSQL 8.4 offered with OpenSUSE 11.2. It works exceptionally well. I have installed the contrib package in order to use the functions provided by XML2/pg_xml as explained in this page: http://www.postgresql.org/docs/8.4/static/xml2.html [ but it's not there ] Did you actually install the module? This involves both making the pgxml.so file available, and running the pgxml.sql script in each database you want to use the functions in. See http://www.postgresql.org/docs/8.4/static/contrib.html for a general description of installing contrib modules. regards, tom lane It is now sorted. Thanks Franclin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
R: Re: [GENERAL] Function with DEFAULT arguments
Ok, thanks for the quick reply and the link!
Dario
Messaggio originale
Da: dep...@depesz.com
Data: 12/03/2010 17.38
A: dario@libero.itdario@libero.it
Cc: pgsql-general@postgresql.org
Ogg: Re: [GENERAL] Function with DEFAULT arguments
On Fri, Mar 12, 2010 at 05:28:57PM +0100, dario@libero.it wrote:
Hello,
I'm trying to use the DEFAULT option to pass parameters to the arguments of
a
function.
When I call that function, how can I change the default value of some
arguments and leave as default the value of other arguments? In other
words, is
there a way to 'call' the arguments by their names so to specify which
should
have their default value changed?
Here's a toy example, a function that takes three strings as arguments and
concatenate them:
CREATE FUNCTION test_default(string1 text default 'a', string2 text
default
'b', string3 text default 'c') RETURNS text AS $$
BEGIN
RETURN string1 || string2 || string3;
END;
$$ language 'plpgsql';
-- Only default args:
SELECT test_default(); -- abc
-- With custom values:
SELECT test_default('X', 'Y', 'Z'); -- XYZ
-- Now, how can I leave as default the 1st and 3rd argument (string1 and
string3) and change only the second one (string2)? I would like to do
something
like:
You can't unless you're on PostgreSQL 9.0:
http://www.depesz.com/index.php/2009/11/17/waiting-for-8-5-named-function-
arguments/
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
--
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] Function with DEFAULT arguments
dario@libero.it dario@libero.it writes: I'm trying to use the DEFAULT option to pass parameters to the arguments of a function. When I call that function, how can I change the default value of some arguments and leave as default the value of other arguments? You can only omit arguments from right to left, so basically what this requires is some foresight while choosing the function's argument order. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function with DEFAULT arguments
Tom Lane wrote: dario@libero.it dario@libero.it writes: I'm trying to use the DEFAULT option to pass parameters to the arguments of a function. When I call that function, how can I change the default value of some arguments and leave as default the value of other arguments? You can only omit arguments from right to left, so basically what this requires is some foresight while choosing the function's argument order. regards, tom lane What do you think about allowing 'default' as a parameter the way we do when inserting a record? So if a param has a default value, the function call may look like: SELECT foo(default, 'hello', 43) Scott Bailey -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [RFC] Comments on PostPic project
Hello, I would like to receive comments/suggestions about this project: http://github.com/drotiro/postpic. In short, it's an extension that enables image processing within the database, adding a new type (image) and several functions. The SQL and Java interfaces are documented on the project's wiki, so I'm not talking about these here, but instead present some detail on the datatype's implementation. The image is represented by a struct containing some attributes (dimensions, some exif tag: shoot date, exposure time...) and a large object holding the actual image data. The idea is to have attributes stored directly to allow for efficient searching, while the large object seemed a reasonable choice to store the possibly large image data (what are the LOBs for?). With the current large objects implementation, when a new lo is created it lives in the pg_largeobjects table, until someone calls lo_unlink on it. In my case: I create the lo on behalf of the user, then store its oid in the image's internal representation. At this point, the image can be inserted in a table, processed and so on, but when it gets deleted the corresponding lo remains dangling, unless someone or something (eg. a trigger) takes care on destroying it. Is there a way of placing some kind of hook on an object's deletion? A clean way to do a reference counting on large objects? To avoid polluting pg_largeobjects, almost all of the image processing functions in PostPic return a 'temporary_image' object, which is just an alias on bytea. (Btw: I defined it using a DOMAIN. A better way?). Temporary images can be converted back to images when needed via a cast (often there is a variant of the function doing this automatically). Thanks in advance for your suggestions and contribution, Domenico.
Re: [GENERAL] Function with DEFAULT arguments
Scott Bailey arta...@comcast.net writes: Tom Lane wrote: You can only omit arguments from right to left, so basically what this requires is some foresight while choosing the function's argument order. What do you think about allowing 'default' as a parameter the way we do when inserting a record? Seems like a nonstarter because of overloading considerations --- what are you going to do if there are multiple possible matches? In any case, the match-arguments-by-name solution available in PG 9.0 seems a whole lot superior to complicating positional match even more. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [RFC] Comments on PostPic project
Hello, I would like to receive comments/suggestions about this project: http://github.com/drotiro/postpic. In short, it's an extension that enables image processing within the database, adding a new type (image) and several functions. The SQL and Java interfaces are documented on the project's wiki, so I'm not talking about these here, but instead present some detail on the datatype's implementation. The image is represented by a struct containing some attributes (dimensions, some exif tag: shoot date, exposure time...) and a large object holding the actual image data. The idea is to have attributes stored directly to allow for efficient searching, while the large object seemed a reasonable choice to store the possibly large image data (what are the LOBs for?). With the current large objects implementation, when a new lo is created it lives in the pg_largeobjects table, until someone calls lo_unlink on it. In my case: I create the lo on behalf of the user, then store its oid in the image's internal representation. At this point, the image can be inserted in a table, processed and so on, but when it gets deleted the corresponding lo remains dangling, unless someone or something (eg. a trigger) takes care on destroying it. Is there a way of placing some kind of hook on an object's deletion? A clean way to do a reference counting on large objects? To avoid polluting pg_largeobjects, almost all of the image processing functions in PostPic return a 'temporary_image' object, which is just an alias on bytea. (Btw: I defined it using a DOMAIN. A better way?). Temporary images can be converted back to images when needed via a cast (often there is a variant of the function doing this automatically). Thanks in advance for your suggestions and contribution, Domenico.
Re: [GENERAL] Joining one-to-one and one-to-many tables
Garrett Murphy wrote:
Jeff:
I may not fully understand the situation or the data you're trying to retrieve, but if
you're trying to get show me all trainers whose current status is Pending, I
would go backwards to how you're doing it. If you're only concerned about those records
with tr_date_name = Pending, then you only need to query for those specific records and
join to trainers_trainer_dates.
SELECT
pp_id,
pp_trainer_id,
name,
tr_status,
trs_tr_date_recorded
FROM
trainer_dates
JOIN trainers_trainer_dates ON tr_date_id = trs_tr_date_id
JOIN people ON trs_tr_date_pp_id = pp_id
WHERE
tr_status='Pending'
Thanks for the reply. This doesn't quite do what I need but I may have tried
to oversimplify my question too much.
What I need is to be able to join the two one-to-one tables with several
one-to-many tables and retrieve the most recent entry from each based on the
most recent timestamp for that record.
In my example, I used the sample query of Show me all trainers who's current
status is Pending, but that could also be show me all trainers and include
in that their status, their education level, their trainer level, and so on.
Each of those three are held in a separate table that contains a pointer to
the person, to their trainer record, to a level or status id, and a timestamp.
I would have thought that using a subselect to first retrieve the latest value
from the one-to-many tables would do it but I keep getting results returning
more than one value, as below where I got two results instead of just one.
I think I have found a solution, though, on page 324 of Joe Celko's SQL for
Smarties. If I re-write the query pulling the latest status for all trainers
as this:
SELECT trs_tr_date_pp_id, tr_date_name FROM trainers_trainer_dates as ttd1
JOIN trainer_dates on tr_date_id = trs_tr_date_id
WHERE NOT EXISTS (
SELECT * FROM trainers_trainer_dates as ttd2 where
ttd1.trs_tr_date_pp_id = ttd2.trs_tr_date_pp_id and
ttd1.trs_tr_date_recorded ttd2.trs_tr_date_recorded;
I get a list of all trainers and their most current status and I can then
further restrict it to whatever status as needed.
I knew I was making it overly complex but I sure needed a nudge in the right
direction to figure this out.
Jeff
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Jeff Ross
Sent: Thursday, March 11, 2010 11:37 AM
To: PostgreSQL
Subject: [GENERAL] Joining one-to-one and one-to-many tables
I'm stumped about the best was to retrieve the most recent entry in a
one-to-many type of table and combine it with a more standard query
that joins one-to-one.
I have defined these:
jr...@wykids localhost# \d trainer_dates
Table public.trainer_dates
Column | Type | Modifiers
+-+-
tr_date_id | integer | not null default
nextval('trainer_dates_tr_date_id_seq'::regclass)
tr_date_short_name | text| not null
tr_date_name | text| not null
tr_date_active | boolean | default false
Indexes:
trainer_dates_pkey PRIMARY KEY, btree (tr_date_id)
Referenced by:
TABLE trainers_trainer_dates CONSTRAINT
trainers_trainer_dates_trs_tr_date_id_fkey FOREIGN KEY (trs_tr_date_id)
REFERENCES trainer_dates(tr_date_id)
and
jr...@wykids localhost# \d trainers_trainer_dates
Table public.trainers_trainer_dates
Column|Type | Modifiers
--+-+--
trs_tr_date_pp_id| integer |
trs_tr_date_tr_id| integer |
trs_tr_date_id | integer |
trs_tr_date | date| default
('now'::text)::date
trs_tr_date_recorded | timestamp without time zone | default now()
Foreign-key constraints:
trainers_trainer_dates_trs_tr_date_id_fkey FOREIGN KEY (trs_tr_date_id)
REFERENCES trainer_dates(tr_date_id)
trainers_trainer_dates_trs_tr_date_pp_id_fkey FOREIGN KEY
(trs_tr_date_pp_id) REFERENCES people(pp_id)
trainers_trainer_dates_trs_tr_date_tr_id_fkey FOREIGN KEY
(trs_tr_date_tr_id) REFERENCES trainers(tr_id)
Here are the records in trainer_dates:
jr...@wykids localhost# select * from trainer_dates;
tr_date_id | tr_date_short_name |tr_date_name| tr_date_active
+++
1 | tr_active_date | Active | t
2 | tr_inactive_date | Inactive | t
3 | tr_destroyed_date | Destroyed | t
4 | tr_pending_date| Pending| t
5 |
Re: [GENERAL] ERROR: invalid byte sequence for encoding UTF8: 0x93 Error
Hi Michal, Thanks for the info. However, the text that the user entered is all in English letters. I saw this error, when I restored my database with the pg_dump --insert option. That row didn't get inserted to the table. What's interesting about this problem is that when I manually entered the data to the database via psql, this record made it without any problems. It's still a mystery to me . Mary -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Michal Pawlikowski Sent: Monday, March 08, 2010 4:38 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] ERROR: invalid byte sequence for encoding UTF8: 0x93 Error On Mon, Mar 8, 2010 at 9:17 AM, Albe Laurenz laurenz.a...@wien.gv.at wrote: Mary Y Wang wrote: I got the following error and not sure how to fix it. psql:/tmp/030610dumpfile.txt:4369: ERROR: invalid byte sequence for encoding UTF8: 0x93 look here: http://www.depesz.com/index.php/2010/03/07/error-invalid-byte-sequence-for-encoding/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: invalid byte sequence for encoding UTF8: 0x93 Error
Wang, Mary Y escribió: Hi Michal, Thanks for the info. However, the text that the user entered is all in English letters. I saw this error, when I restored my database with the pg_dump --insert option. That row didn't get inserted to the table. What's interesting about this problem is that when I manually entered the data to the database via psql, this record made it without any problems. It's still a mystery to me . Well, 0x93 is certainly not an english letter; most likely it's a win1252-encoded curved single quote. That's not within the ASCII definition. You can probably insert the data by declaring SET client_encoding TO 'win1252' at the start of the file. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: invalid byte sequence for encoding UTF8:0x93 Error
Thanks Alvaro. What do I do with a apostrophe (such as I've)? Mary Original Message- From: Alvaro Herrera [mailto:alvhe...@commandprompt.com] Sent: Friday, March 12, 2010 12:48 PM To: Wang, Mary Y Cc: Michał Pawlikowski; pgsql-general@postgresql.org Subject: Re: [GENERAL] ERROR: invalid byte sequence for encoding UTF8:0x93 Error Wang, Mary Y escribió: Hi Michal, Thanks for the info. However, the text that the user entered is all in English letters. I saw this error, when I restored my database with the pg_dump --insert option. That row didn't get inserted to the table. What's interesting about this problem is that when I manually entered the data to the database via psql, this record made it without any problems. It's still a mystery to me . Well, 0x93 is certainly not an english letter; most likely it's a win1252-encoded curved single quote. That's not within the ASCII definition. You can probably insert the data by declaring SET client_encoding TO 'win1252' at the start of the file. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: invalid byte sequence for encoding UTF8:0x93 Error
Ok. What was I thinking, the curved single quote that you talked about is the apostrophe. It makes sense now. Thanks Mary -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Wang, Mary Y Sent: Friday, March 12, 2010 12:51 PM To: Alvaro Herrera Cc: Michał Pawlikowski; pgsql-general@postgresql.org Subject: Re: [GENERAL] ERROR: invalid byte sequence for encoding UTF8:0x93 Error Thanks Alvaro. What do I do with a apostrophe (such as I've)? Mary Original Message- From: Alvaro Herrera [mailto:alvhe...@commandprompt.com] Sent: Friday, March 12, 2010 12:48 PM To: Wang, Mary Y Cc: Michał Pawlikowski; pgsql-general@postgresql.org Subject: Re: [GENERAL] ERROR: invalid byte sequence for encoding UTF8:0x93 Error Wang, Mary Y escribió: Hi Michal, Thanks for the info. However, the text that the user entered is all in English letters. I saw this error, when I restored my database with the pg_dump --insert option. That row didn't get inserted to the table. What's interesting about this problem is that when I manually entered the data to the database via psql, this record made it without any problems. It's still a mystery to me . Well, 0x93 is certainly not an english letter; most likely it's a win1252-encoded curved single quote. That's not within the ASCII definition. You can probably insert the data by declaring SET client_encoding TO 'win1252' at the start of the file. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] static build from source on windows
Hi, I am trying to build postgres static with mysis and as far as I can find any hints with google I tried the following: ./configure --without-zlib --disable-shared make It stops with this error: make[3]: Entering directory `/home/postgresql-8.4.2-src/src/interfaces/libpq' make[3]: *** No rule to make target `libpq.dll', needed by `all-shared-lib'. Stop. What is the correct way to get a static libpq? I also found something like make install enable_shared=no but that did not help. Jan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] unexplained autovacuum to prevent wraparound
It appears to me that in my 8.4.0 system, autovacuum is running to prevent wraparound contrary to the documentation. I have it set to a tables' relfrozenxid has to get to 1.5 billion before that kicks in: show autovacuum_freeze_max_age; 15 show vacuum_freeze_table_age; 13 show vacuum_freeze_min_age; 10 Table foo has an age just over 1 billion, still well under the freeze_table_age: select age(relfrozenxid) from pg_class where relname='foo'; age 1055823634 yet, I see this in pg_stat_activity: autovacuum: VACUUM public.foo (to prevent wraparound) One possibly interesting thing is that this seems to have started just after I set foo's autovacuum_analyze_scale_factor to 0.01, since I wanted more frequent analyze runs. I wonder if that could be related. Any ideas? These wraparound autovacuums are taking up my vacuum workers so no analyze workers can run. Thanks! Gordon -- View this message in context: http://old.nabble.com/unexplained-autovacuum-to-prevent-wraparound-tp27883825p27883825.html Sent from the PostgreSQL - general mailing list archive at Nabble.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: [GENERAL] unexplained autovacuum to prevent wraparound
Gordon Shannon escribió: One possibly interesting thing is that this seems to have started just after I set foo's autovacuum_analyze_scale_factor to 0.01, since I wanted more frequent analyze runs. I wonder if that could be related. You probably set the other values to 0, which includes the freeze age. You need to set it (and all other values) to -1 instead. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unexplained autovacuum to prevent wraparound
This is 8.4, there is no pg_autovacuum table. I set it like this: alter table foo set (autovacuum_analyze_scale_factor=0.01); On Fri, Mar 12, 2010 at 4:31 PM, Alvaro Herrera alvhe...@commandprompt.comwrote: Gordon Shannon escribió: One possibly interesting thing is that this seems to have started just after I set foo's autovacuum_analyze_scale_factor to 0.01, since I wanted more frequent analyze runs. I wonder if that could be related. You probably set the other values to 0, which includes the freeze age. You need to set it (and all other values) to -1 instead. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: [GENERAL] unexplained autovacuum to prevent wraparound
On Fri, 2010-03-12 at 16:45 -0700, Gordon Shannon wrote: This is 8.4, there is no pg_autovacuum table. I set it like this: alter table foo set (autovacuum_analyze_scale_factor=0.01); That is 1% changes. I think you want .10 Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- 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] Function with DEFAULT arguments
Messaggio originale
Da: t...@sss.pgh.pa.us
Data: 12/03/2010 17.51
A: dario@libero.itdario@libero.it
Cc: pgsql-general@postgresql.org
Ogg: Re: [GENERAL] Function with DEFAULT arguments
dario@libero.it dario@libero.it writes:
I'm trying to use the DEFAULT option to pass parameters to the arguments of
a
function.
When I call that function, how can I change the default value of some
arguments and leave as default the value of other arguments?
You can only omit arguments from right to left, so basically what this
requires is some foresight while choosing the function's argument order.
regards, tom lane
Thanks for replies!
Would it be a very horrible workaround to pass a single string to the function
which contains the user's parameters? This string then is parsed into the
individual arguments/defaults inside the function. In this way there is no need
to have arguments in any order.
Example using plpythonu:
CREATE OR REPLACE FUNCTION test_default(arg_string text) RETURNS text AS
$$
## List of pseudo-arguments the function can take
arg_1= 'arg_1'
arg_2= 'arg_2'
arg_3= 'arg_3'
## Convert the argument string to a dictionary
arg_dict= eval('{' + arg_string + '}')
## Retrieve user's parameters and assign defaults
try:
arg_1= arg_dict[arg_1]
except:
arg_1= 'A'
try:
arg_2= arg_dict[arg_2]
except:
arg_2= 'B'
try:
arg_3= arg_dict[arg_3]
except:
arg_3= 'C'
## Do something with the parameters
return('One: ' + arg_1 + '; Two: ' + arg_2 + '; Three: ' + arg_3)
$$
language 'plpythonu';
-- Execute with default 'pseudo-arguments' only:
SELECT test_default($$ $$);
-- One: A; Two: B; Three: C
-- With arg_2 as default:
SELECT test_default($$ arg_3:'z', arg_1:'x' $$);
-- One: x; Two: B; Three: z
All the best
Dario
--
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] unexplained autovacuum to prevent wraparound
Thanks, but I do want 1%. On Fri, Mar 12, 2010 at 5:19 PM, Joshua D. Drake j...@commandprompt.comwrote: On Fri, 2010-03-12 at 16:45 -0700, Gordon Shannon wrote: This is 8.4, there is no pg_autovacuum table. I set it like this: alter table foo set (autovacuum_analyze_scale_factor=0.01); That is 1% changes. I think you want .10 Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- If I had more time, I could have written you a shorter letter. (Blaise Pascal)
[GENERAL] How to remove superuser
Hello im running Windows XP and removed an older version of Postgres using windows software remover, now when i try to install the newest one it keeps asking for a password from Superuser? Thanks
Re: [GENERAL] How to remove superuser
Harry Gold wrote: Hello im running Windows XP and removed an older version of Postgres using windows software remover, now when i try to install the newest one it keeps asking for a password from Superuser? Thanks go into Windows' Local Users and Groups in Computer Management, and set a random password for the existing 'postgres' user account (its a special very UN-privileged account, has only the priv to run as a service), then tell the installer what that password is. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to remove superuser
John R Pierce wrote: Harry Gold wrote: Hello im running Windows XP and removed an older version of Postgres using windows software remover, now when i try to install the newest one it keeps asking for a password from Superuser? Thanks go into Windows' Local Users and Groups in Computer Management, and set a random password for the existing 'postgres' user account (its a special very UN-privileged account, has only the priv to run as a service), then tell the installer what that password is. Also, you can do: NET USER postgres /delete as an administrator. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to remove superuser
Bruce Momjian wrote: Also, you can do: NET USER postgres /delete as an administrator. or NET USER postgres * and then enter a new password. I dislike deleting the account in case there are any files left behind by the old postgres such as the old pgdata directory, a new one is a different account. but on vista and win7, with the UAC privilege masking thing, you have to be runnign CMD as Administrator before you can do this, as the regular cmd prompt is unprivileged and won't invoke the UAC elevation stuff. you can right-click Run As Admin the shortcut to CMD to get a privileged command prompt. -- 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] Function with DEFAULT arguments
2010/3/13 dario@libero.it dario@libero.it:
Messaggio originale
Da: t...@sss.pgh.pa.us
Data: 12/03/2010 17.51
A: dario@libero.itdario@libero.it
Cc: pgsql-general@postgresql.org
Ogg: Re: [GENERAL] Function with DEFAULT arguments
dario@libero.it dario@libero.it writes:
I'm trying to use the DEFAULT option to pass parameters to the arguments of
a
function.
When I call that function, how can I change the default value of some
arguments and leave as default the value of other arguments?
You can only omit arguments from right to left, so basically what this
requires is some foresight while choosing the function's argument order.
regards, tom lane
Thanks for replies!
Would it be a very horrible workaround to pass a single string to the function
which contains the user's parameters? This string then is parsed into the
individual arguments/defaults inside the function. In this way there is no
need
to have arguments in any order.
Example using plpythonu:
CREATE OR REPLACE FUNCTION test_default(arg_string text) RETURNS text AS
$$
## List of pseudo-arguments the function can take
arg_1= 'arg_1'
arg_2= 'arg_2'
arg_3= 'arg_3'
## Convert the argument string to a dictionary
arg_dict= eval('{' + arg_string + '}')
## Retrieve user's parameters and assign defaults
try:
arg_1= arg_dict[arg_1]
except:
arg_1= 'A'
try:
arg_2= arg_dict[arg_2]
except:
arg_2= 'B'
try:
arg_3= arg_dict[arg_3]
except:
arg_3= 'C'
## Do something with the parameters
return('One: ' + arg_1 + '; Two: ' + arg_2 + '; Three: ' + arg_3)
$$
language 'plpythonu';
-- Execute with default 'pseudo-arguments' only:
SELECT test_default($$ $$);
-- One: A; Two: B; Three: C
-- With arg_2 as default:
SELECT test_default($$ arg_3:'z', arg_1:'x' $$);
-- One: x; Two: B; Three: z
All the best
Dario
what is sense of this?
It is a problem, because pg selection of adequate function is based on
type compatibility. and varchar isn't compatible with n - params -
mainly we don't see, what is content of some string (if there is one
parameter, or ten parameters).
If you need carry some unspecified number of parameters, then use
function with hstore type parameter.
Regards
Pavel Stehule
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
