Re: [GENERAL] two or more pg installations running as the same user
2008/11/26 Mikko Partio [EMAIL PROTECTED] You know you don't need separate clusters (ie. installations) to have multiple databases? obvioiusly. so far I just do: mkdir $INST export PGDATA=$INST export PGPORT=$INSTPORT initdb pg_ctl start .. and that's it folks. -- GJ
Re: [GENERAL] Postgres 8.3 only uses seq scan
On 11/26/2008 06:44 PM, [EMAIL PROTECTED] wrote: Try running EXPLAIN ANALYZE - that gives much more information. For example it may show differences in number of rows between the two machines, that the statistics are not up to date, etc. Thanks a lot for this tip -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager] [ E-Graphics Communications, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.jp ] signature.asc Description: OpenPGP digital signature
Re: [GENERAL] slow, long-running 'commit prepared'
John Smith [EMAIL PROTECTED] writes: I have a pg instance with 700GB of data, almost all of which is in one table. When I PREPARE and then COMMIT PREPARED a transaction that reads writes to a large fraction of that data (about 10%, effectively randomly chosen rows and so every file in the table is modified), the COMMIT PREPARED sometimes takes a very long time--2 to 5 minutes. Is this expected? It's impossible to say without knowing more about what the transaction did. But one piece of data you could check easily is the size of the 2PC state file (look into $PGDATA/pg_twophase/). 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] case when evaluating else condition anyway?
I've case when ''=extinput then null else extinput::timestamp end now when extinput='' it seems that else extinput::timestamp get evaluated anyway and I get: invalid input syntax for type timestamp: The purpose of all this gymnic would be to convert empty string to null and everything else to a timestamp. Is there any cleaner functional way that doesn't involve prepared statement etc... since the whole exercise is caused by an null-impaired DB API (aka MySQLish). -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case when evaluating else condition anyway?
Ivan Sergio Borgonovo [EMAIL PROTECTED] writes: I've case when ''=extinput then null else extinput::timestamp end now when extinput='' it seems that else extinput::timestamp get evaluated anyway and I get: invalid input syntax for type timestamp: I think you are leaving off a large percentage of the truth. Is extinput actually a variable, or do you mean that you are substituting a literal string there? 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] Postgres 8.3 only uses seq scan
Try running EXPLAIN ANALYZE - that gives much more information. For
example it may show differences in number of rows between the two
machines, that the statistics are not up to date, etc.
regards
Tomas
Hi,
I have system here with Debian/Testing and the latest 8.2 and 8.3
database installed.
on a blank database I create two very simple tables
Table public.foo
Column | Type| Modifiers
+---+--
foo_id | integer | not null default
nextval('foo_foo_id_seq'::regclass)
test | character varying |
Indexes:
foo_pkey PRIMARY KEY, btree (foo_id)
Table public.bar
Column | Type| Modifiers
+---+--
bar_id | integer | not null default
nextval('bar_bar_id_seq'::regclass)
foo_id | integer | not null
test | character varying |
Indexes:
bar_pkey PRIMARY KEY, btree (bar_id)
bar_foo_id_idx btree (foo_id)
Foreign-key constraints:
bar_foo_id_fkey FOREIGN KEY (foo_id) REFERENCES foo(foo_id) MATCH
FULL ON UPDATE CASCADE ON DELETE CASCADE
now if I run a simple join query over both tables Postgres 8.2 gives
this back for the explain:
# explain select * from foo f, bar b where f.foo_id = b.foo_id;
QUERY PLAN
Nested Loop (cost=0.00..33.14 rows=3 width=76)
- Index Scan using bar_foo_id_idx on bar b (cost=0.00..12.30
rows=3 width=40)
- Index Scan using foo_pkey on foo f (cost=0.00..6.93 rows=1
width=36)
Index Cond: (f.foo_id = b.foo_id)
but on the 8.3 version i get this back
# explain select * from foo f, bar b where f.foo_id = b.foo_id;
QUERY PLAN
--
Hash Join (cost=1.07..2.14 rows=3 width=24)
Hash Cond: (b.foo_id = f.foo_id)
- Seq Scan on bar b (cost=0.00..1.03 rows=3 width=14)
- Hash (cost=1.03..1.03 rows=3 width=10)
- Seq Scan on foo f (cost=0.00..1.03 rows=3 width=10)
once I insert a million rows he does use the index:
# explain select * from foo f, bar b where f.foo_id = b.foo_id;
QUERY PLAN
---
Nested Loop (cost=0.00..26.39 rows=9 width=35)
- Seq Scan on foo f (cost=0.00..1.03 rows=3 width=21)
- Index Scan using bar_foo_id_idx on bar b (cost=0.00..8.42 rows=3
width=14)
Index Cond: (b.foo_id = f.foo_id)
I have seen this behavior on all of my postgres 8.3 installs. The
indexes are there, auto vacuum is turned on. even a reindex of the
tables does not help. The configuration files are identical in grounds
of memory usage, query planning, etc.
I see this on RPM packages for RedHat Enterprise, self compiled for
FreeBSD 4, and debian packages. I am seriously very very confused.
What can I do to debug this further, or find out why this happens?
Does this mean Postgres 8.3 thinks a sequence scan is faster than an
index scan? Even on tables with hundred thousands rows?
--
[ Clemens Schwaighofer -=:~ ]
[ IT Engineer/Manager]
[ E-Graphics Communications, TEQUILA\ Japan IT Group ]
[6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.jp ]
--
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] case when evaluating else condition anyway?
On Wed, Nov 26, 2008 at 02:53:07PM +0100, Ivan Sergio Borgonovo wrote: case when ''=extinput then null else extinput::timestamp end I'd tend to use nullif(extinput,'')::timestamp for this sort of thing. It's not going to do anything much different from what you're doing, but may end up evaluating extinput less which may be a good thing. Is there any cleaner functional way that doesn't involve prepared statement etc... since the whole exercise is caused by an null-impaired DB API (aka MySQLish). As Tom said, maybe if you could send a larger example it would help. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with langage encoding
I've inherited a database system (including source)... It's Russian text (which I don't speak, but I need to work on it). On my screen it shows correctly, but in the database it shows a mess... from trying with a web browser. It appears that the data in the database is encoded with ISO-8859-1 (my browser and the database viewed with PGAdmin match when my browser has ISO-8859-1 encoding) and it shows correctly on screen in my browser when I use the WIN1251 encoding... The problem is that the database is UTF8, and it won't let me use the convert_to function because it says that the characters don't exist... What I need to do is to pull the UTF8 from the database, tell postgres that it's 8859-1, and then convert it to WIN1251... How? Thank you for any assistance! Ron -- 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] Effect of stopped status collector process
Merlin Moncure escribió: On Tue, Nov 25, 2008 at 9:03 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Why do we _have_ to write the file to disk? I wonder if it would work to store the file in a mmaped memory region and have the readers get data from there. We could have more than one copy, reference-counted so that they can be removed when the old readers are gone. what about fifo files...would they be appropriate for something like this? Doubtful -- the collector would have to write the contents every time someone wanted to read it, and nobody could open it while someone else is reading (or they'd read from the middle of the contents). -- 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] Problem with langage encoding
On 26/11/2008 14:40, Ronald Vyhmeister wrote: The problem is that the database is UTF8, and it won't let me use the convert_to function because it says that the characters don't exist... What I need to do is to pull the UTF8 from the database, tell postgres that it's 8859-1, and then convert it to WIN1251... How? If the DB is in UTF8, you ought to be able to issue set client_encoding to 'WIN1251' after connection, and the conversion happens automatically. See: http://www.postgresql.org/docs/8.3/static/multibyte.html#AEN27483 HTH, Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Problem with langage encoding
The problem is that the database is UTF8, and it won't let me use the convert_to function because it says that the characters don't exist... What I need to do is to pull the UTF8 from the database, tell postgres that it's 8859-1, and then convert it to WIN1251... How? If the DB is in UTF8, you ought to be able to issue set client_encoding to 'WIN1251' after connection, and the conversion happens automatically. See: http://www.postgresql.org/docs/8.3/static/multibyte.html#AEN27483 HTH, Ray. Wish it would work... when I do it, I get: ERROR: character 0xc3bd of encoding UTF8 has no equivalent in WIN1251 ** Error ** ERROR: character 0xc3bd of encoding UTF8 has no equivalent in WIN1251 SQL state: 22P05 The DB is storing it UTF8, but it is really 8859-1... I need to force PG to think that it's 8859-1 in spite of what it thinks it may be... Ron -- 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] case when evaluating else condition anyway?
On Wed, 26 Nov 2008 14:18:44 +
Sam Mason [EMAIL PROTECTED] wrote:
On Wed, Nov 26, 2008 at 02:53:07PM +0100, Ivan Sergio Borgonovo
wrote:
case when ''=extinput then null else extinput::timestamp end
I'd tend to use nullif(extinput,'')::timestamp for this sort of
Thanks it was on the top of my fingers... BTW it was a substituted
literal ala printf before anything was piped to postgresql, so
actually postgresql would receive something as the examples below.
Still why does the else condition get evaluated anyway when at least
one of the when condition is true?
thing. It's not going to do anything much different from what
you're doing, but may end up evaluating extinput less which may
be a good thing.
Is there any cleaner functional way that doesn't involve prepared
statement etc... since the whole exercise is caused by an
null-impaired DB API (aka MySQLish).
As Tom said, maybe if you could send a larger example it would
help.
as a more self contained example:
select case when ''='' then null else ''::timestamp end;
vs.
select nullif('','')::timestamp;
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with langage encoding
Ronald Vyhmeister [EMAIL PROTECTED] writes: The DB is storing it UTF8, but it is really 8859-1... I need to force PG to think that it's 8859-1 in spite of what it thinks it may be... Dump the database, change the set client_encoding command in the resulting file, reload. 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] case when evaluating else condition anyway?
On Wed, Nov 26, 2008 at 04:17:40PM +0100, Ivan Sergio Borgonovo wrote: as a more self contained example: select case when ''='' then null else ''::timestamp end; Tee hee, I've just realized what you're doing. You've got the cast in the wrong place! Try: select case when ''='' then null else '' end::timestamp; I was reading it this way around automatically! Literals are always expanded immediately and hence you're getting the error. You want the case statement to work with strings and only cast it when you know it's actually safe to make the move from a string literal to a timestamp value. 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] case when evaluating else condition anyway?
Ivan Sergio Borgonovo [EMAIL PROTECTED] writes: Still why does the else condition get evaluated anyway when at least one of the when condition is true? Because 'literal'::type is a literal of that type; it does not represent a run-time conversion. I think we document that under the discussion of constants in the syntax chapter. 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] pg_get_serial_sequence Strangeness/Unreliable?
On 25-Nov-08, at 10:44 AM, Tom Lane wrote:
Jeff MacDonald [EMAIL PROTECTED] writes:
The issue, is that when I run pg_get_serial_sequence on a particular
table/column it returns NULL.
Does the column actually own that sequence? Or is its default just
something that was inserted manually?
Hi Tom, so far as I know the table owns the serial in so much as
when i do a \d of the table it says this
status_id | integer | not null default
nextval('status_status_id_seq'::regclass)
How else can I check?
--
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] pg_get_serial_sequence Strangeness/Unreliable?
On 25-Nov-08, at 10:51 AM, V S P wrote: Did you first insert into public.news_status insert into public.news_status (status_id) values (DEFAULT) and then get the sequence? Hi VSP I'm not sure what relevance this has, a sequence already exists whether you insert into it or not. Just sometimes if you don't insert or select, you cannot get the currval for that session. Also since you have a domain 'public' I personally always do 'set searc_path to public' before doing any SQLs -- this way I know that I do not need to prefix my table names with 'public' all the time. I don't have a need for multiple schemas right now, so public is assumed. Thanks tho. jeff. -- 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] pg_get_serial_sequence Strangeness/Unreliable?
Jeff MacDonald wrote:
Hi Tom, so far as I know the table owns the serial in so much as when i
do a \d of the table it says this
status_id | integer | not null default
nextval('status_status_id_seq'::regclass)
How else can I check?
He means ALTER SEQUENCE ... OWNED BY
I don't know how you can ensure that it is, short of
begin;
drop table status;
\d status_status_id_seq
-- verify that the sequence exists; if owned, it should have been dropped too
rollback;
--
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] [ADMIN] PgAgent Job Scehduler is NOT running
Try to follow the steps given in following link - http://wiki.postgresql.org/wiki/Image:PgAgent_for_windows.doc Date: Tue, 25 Nov 2008 02:32:46 -0800From: [EMAIL PROTECTED]: Re: [ADMIN] PgAgent Job Scehduler is NOT runningTo: [EMAIL PROTECTED]; [EMAIL PROTECTED]: pgsql-general@postgresql.org Service is running. After getting frustrated I dropped the pgagent schema and reworked. But the result is same. ???. Even entries in the concerned pga_tables is also very clear. Perhaps the trigger is not working . From: Vishal Arora [EMAIL PROTECTED]To: [EMAIL PROTECTED]: Tuesday, November 25, 2008 3:08:05 PMSubject: RE: [ADMIN] PgAgent Job Scehduler is NOT running Is the service running properly, try to schedule a new job and see the results. Date: Mon, 24 Nov 2008 23:23:15 -0800From: [EMAIL PROTECTED]: Re: [ADMIN] PgAgent Job Scehduler is NOT runningTo: [EMAIL PROTECTED]; [EMAIL PROTECTED]: pgsql-general@postgresql.org No error message appeared. Because NO statistics is available for the job. It reflects that it is not running. - Forwarded Message From: Vishal Arora [EMAIL PROTECTED]To: [EMAIL PROTECTED]; [EMAIL PROTECTED]: [EMAIL PROTECTED]: Tuesday, November 25, 2008 11:47:33 AMSubject: RE: [ADMIN] PgAgent Job Scehduler is NOT running What is the error message you are getting. What is the interval you have set for scheduling the job. Date: Sun, 23 Nov 2008 23:51:46 -0800From: [EMAIL PROTECTED]: [ADMIN] PgAgent Job Scehduler is NOT runningTo: [EMAIL PROTECTED]: [EMAIL PROTECTED] Dear all,I installed PgAgent and started its service and successfully scheduled a backup and got 100% result. Now the same Job is not working even I reinstalled PgAgent but failed to get result. Regards,Abdul Rehman. Team India gets set to thwart Australia's quest for the final frontier. Catch the action on MSN Try it now! Calling TV buffs! Get TV listings, gossip on your fave stars and updates on hot new shows Try it now! _ Searching for the best deals on travel? Visit MSN Travel. http://in.msn.com/coxandkings
Re: [GENERAL] Problem with langage encoding
Ronald Vyhmeister wrote:
The problem is that the database is UTF8, and it won't let me use the
convert_to function because it says that the characters don't exist...
What
I need to do is to pull the UTF8 from the database, tell postgres that
it's
8859-1, and then convert it to WIN1251... How?
If the DB is in UTF8, you ought to be able to issue
set client_encoding to 'WIN1251'
after connection, and the conversion happens automatically. See:
http://www.postgresql.org/docs/8.3/static/multibyte.html#AEN27483
HTH,
Ray.
Wish it would work... when I do it, I get:
ERROR: character 0xc3bd of encoding UTF8 has no equivalent in WIN1251
Which it does not; that character is 쎽 (HANGUL SYLLABLE SSYEG) which
certainly isn't in WIN1251 or in latin-1 (ISO-8859-1). The byte sequence
for this character in UTF-8 is:
0xec 0x8e 0xbd
When decoded as latin-1, those three bytes are interpreted as: ì½ ...
which doesn't seem much more likely to be valid. Are you SURE it's
latin-1, not (say) Koi_r-8?
Python is a useful tool for investigating encoding problems. It draws a
strong distinction between true Unicode strings and byte strings. It
provides quality conversion routines that let you specify the encoding
of the byte string, and that'll throw an exception for nonsensical input.
This lets you take a byte string, decode it according to various
encodings, and see what you land up with. You can also test various
horrible encoding mangling schemes used by apps rather easily. The
python `encodings' module contains a list of all known encodings by all
aliases as `encodings.aliases.aliases'. Here's some code that takes an
input byte string and prints a table of all ways it can be interpreted
according to various encodings:
---
import sys
import encodings
stupid_encodings = ['zlib_codec', 'uu_codec', 'hex_codec', 'bz2_codec']
bs = '\xc3\xbd'
for encoding in set(encodings.aliases.aliases.values()):
sys.stdout.write(u%20s: % encoding)
sys.stdout.flush()
if encoding in stupid_encodings:
continue
try:
print u\%6s\ (%6s) % (bs.decode(encoding),
repr(bs.decode(encoding)))
except UnicodeDecodeError,e:
print u [INVALID]
except LookupError,e:
print u [UNSUPPORTED]
... and here's the output for the data you mentioned:
bz2_codec: cp1140: C¨ (u'C\xa8')
rot_13: ý (u'\xc3\xbd')
cp932: ﾃｽ (u'\uff83\uff7d')
euc_jisx0213: 箪 (u'\u7baa')
cp037: C¨ (u'C\xa8')
hex_codec:cp500: C¨ (u'C\xa8')
uu_codec:big5hkscs: 羸 (u'\u7fb8')
mbcs: [UNSUPPORTED]
euc_jis_2004: 箪 (u'\u7baa')
iso2022_jp_3: [INVALID]
iso2022_jp_2: [INVALID]
iso2022_jp_1: [INVALID]
gbk: 媒 (u'\u5a92')
iso2022_jp_2004: [INVALID]
quopri_codec: [INVALID]
cp424: C¨ (u'C\xa8')
iso2022_jp: [INVALID]
mac_iceland: √Ω (u'\u221a\u03a9')
hp_roman8: û§ (u'\xfb\xa7')
iso2022_kr: [INVALID]
euc_kr: 첵 (u'\uccb5')
cp1254: ý (u'\xc3\xbd')
gb2312: 媒 (u'\u5a92')
cp850: Û (u'\u251c\xa2')
shift_jis: ﾃｽ (u'\uff83\uff7d')
cp852: ├Ż (u'\u251c\u017b')
cp855: ├й (u'\u251c\u0439')
utf_16_le: 뷃 (u'\ubdc3')
cp857: Û (u'\u251c\xa2')
cp775: ├Į (u'\u251c\u012e')
cp1026: C¨ (u'C\xa8')
mac_latin2: √Ĺ (u'\u221a\u0139')
mac_cyrillic: √љ (u'\u221a\u0459')
base64_codec:('')
ptcp154: ГҪ (u'\u0413\u04aa')
euc_jp: 箪 (u'\u7baa')
hz: [INVALID]
utf_8: ý (u'\xfd')
mac_greek: ΟΫ (u'\u039f\u03ab')
utf_7: [INVALID]
mac_turkish: √Ω (u'\u221a\u03a9')
cp949: 첵 (u'\uccb5')
zlib_codec: big5: 羸 (u'\u7fb8')
iso8859_9: ý (u'\xc3\xbd')
iso8859_8: [INVALID]
iso8859_5: УН (u'\u0423\u041d')
iso8859_4: ÃŊ (u'\xc3\u014a')
iso8859_7: Γ½ (u'\u0393\xbd')
iso8859_6: [INVALID]
iso8859_3: [INVALID]
iso8859_2: Ă˝ (u'\u0102\u02dd')
gb18030: 媒 (u'\u5a92')
shift_jis_2004: ﾃｽ (u'\uff83\uff7d')
mac_roman: √Ω (u'\u221a\u03a9')
cp950: 羸 (u'\u7fb8')
utf_16: 뷃 (u'\ubdc3')
iso8859_15: Ü (u'\xc3\u0153')
iso8859_14: ÃẄ (u'\xc3\u1e84')
tis_620: รฝ
[GENERAL] Odd on-update inconsistency
I'm having trouble understanding something I saw in my data from yesterday involving an inconsistency between values in a table and its associated rule-updated log table. For application debugging purposes (effectiveness of web double-submit suppression) we have a rule that creates an entry in a log table whenever the table we are watching is updated: Rule: foo_update_rule AS ON UPDATE TO foo DO INSERT INTO foo_updatelog (old_f1, new_f1, old_f2, new_f2...) VALUES (old.f1, new.f1, old.f2, new.f2...) Where foo is: Column |Type | Modifiers --+-+--- r1| character varying(30) | r2| character varying(30) | ... And foo_updatelog is: Column |Type | Modifiers ---+-+--- updatetime | timestamp without time zone | default now() old_r1 | character varying(30) | new_r1 | character varying(30) | ... Normally this works well to give us the info we need, but this morning I found my table showing f1 equal to 4 and my most recent log entries showing: updatetime | old_f1 | new_f1 ---++ 2008-11-25 17:33:45.537564 | 2 | 3 2008-11-25 17:33:45.539737 | 2 | 3 (The field f1 is a counter - the update query casts the varchar(30) to int, adds one, and casts the result back to varchar(30) due to an unfortunate current requirement on the application side.) I was able to verify that this is a legitimately trapped double-submit but I don't understand why the update log is showing a count of 2-3 twice instead of 2-3 then 3-4 like it typically would. This database is still in 7.4.x land (I know, we're working on it) so if it is related to a known issue I didn't find in subsequent release notes, my apologies. I didn't find anything in the logs that would explain it. Any ideas? Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Date math question
Hi All; I have a table that contains 2 columns ts (a timestamp) and dursec (a float - number of seconds) I want to insert the following into another table: the ts (timestamp column) and a second date which is ts + dursec I tried these select variations with no luck: select ts, ts + interval dursec seconds from tmp2 limit 1; select ts, ts + 'seconds' dursec from tmp2 limit 1; Anyone know the correct syntax for this ? Thanks in advance -- 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] Date math question
On Wed, Nov 26, 2008 at 11:54:33AM -0700, Kevin Kempter wrote: select ts, ts + interval dursec seconds from tmp2 limit 1; select ts, ts + 'seconds' dursec from tmp2 limit 1; select ts, ts + dursec * '1 second'::interval ... depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: [EMAIL PROTECTED] / 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] Date math question
On 26/11/2008 18:54, Kevin Kempter wrote: I have a table that contains 2 columns ts (a timestamp) and dursec (a float - number of seconds) I want to insert the following into another table: the ts (timestamp column) and a second date which is ts + dursec Something like this? - select ts, ts + (dursec * interval '1 second') Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Odd on-update inconsistency
Steve Crawford wrote: I'm having trouble understanding something I saw in my data from yesterday involving an inconsistency between values in a table and its associated rule-updated log table. For application debugging purposes (effectiveness of web double-submit suppression) we have a rule that creates an entry in a log table whenever the table we are watching is updated: Ah, I think you'll find you don't. You have a rule that looks at first glance like it *should* add an entry to your log table. Rules rewrite the query like a macro would and OLD and NEW don't refer to a row but to the entire set of rows. The most common problems you'll see are related to: 1. nextval() / currval() not behaving like you'd think. 2. in particular with multiple-row updates or inserts See the mailing list archives for plenty of discussion, and I think the current manuals have a better description of rules than there used to be. For inserting to a log table you'll want a trigger. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Order by question
Hi All; I'm selecting 3 columns. I want to order the results ascending by col1 and col2 and then descending by col3 Whats the syntax for this? Thanks in advance -- 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] Order by question
On Wed, Nov 26, 2008 at 7:46 PM, Kevin Kempter [EMAIL PROTECTED]wrote: Hi All; I'm selecting 3 columns. I want to order the results ascending by col1 and col2 and then descending by col3 Whats the syntax for this? select * from foo order by a,b asc, c desc; ? -- GJ
Re: [GENERAL] Order by question
Kevin Kempter [EMAIL PROTECTED] schrieb: Hi All; I'm selecting 3 columns. I want to order the results ascending by col1 and col2 and then descending by col3 Whats the syntax for this? order by col1 asc, col2 asc, col3 desc; Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] Order by question
Kevin Kempter wrote on 26.11.2008 20:46: Hi All; I'm selecting 3 columns. I want to order the results ascending by col1 and col2 and then descending by col3 Whats the syntax for this? ORDER BY col1 ASC, col2 ASC, col3 DESC this is documented in the manual -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] problems with special characters
Hi list, I have a problem I have created a database with encoding SQL_ASCII and when I insert the data using a terminal in linux and insert the data for example Insert into mytable values(1,'Eléctrico'); it works fine but if I try to copy this data from a text file doing \copy mytable from textfile, it insert the data but instead of é writes a ? and I don't have any idea how to copy the data without this problem. I really appreciate your help. thanks karina -- 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] pg_get_serial_sequence Strangeness/Unreliable?
Alvaro Herrera [EMAIL PROTECTED] writes: Jeff MacDonald wrote: Hi Tom, so far as I know the table owns the serial in so much as when i do a \d of the table it says this He means ALTER SEQUENCE ... OWNED BY I don't know how you can ensure that it is, short of Well, actually, I think the fact that pg_get_serial_sequence isn't working is the most direct way of knowing that the ownership link isn't there ;-). You could grovel around in pg_depend manually but I'm pretty sure of what the outcome will be. I'd try doing an ALTER SEQUENCE OWNED BY and see if that changes the results. The worst that could happen is it takes ownership away from whichever table actually created the sequence, if there was a different one. (My bet is that you got into this state as a result of using some weird combination of pg_dump and server versions.) 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] pg_get_serial_sequence Strangeness/Unreliable?
On Wed, Nov 26, 2008 at 3:53 PM, Tom Lane [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Jeff MacDonald wrote: Hi Tom, so far as I know the table owns the serial in so much as when i do a \d of the table it says this can we make \d show if the sequence is owned by the table (ie: serial or manually created and owned) or is a manually created and maked default sequence? maybe a flag? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Odd on-update inconsistency
Richard Huxton [EMAIL PROTECTED] writes: Steve Crawford wrote: I'm having trouble understanding something I saw in my data from yesterday involving an inconsistency between values in a table and its associated rule-updated log table. For application debugging purposes (effectiveness of web double-submit suppression) we have a rule that creates an entry in a log table whenever the table we are watching is updated: Ah, I think you'll find you don't. You have a rule that looks at first glance like it *should* add an entry to your log table. We'd have to see the original query (the one the rule acted on) to be sure, but I suspect Richard's diagnosis is correct. For inserting to a log table you'll want a trigger. +1 ... triggers are way less likely to do strange things. 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] Date math question
Hi All; I have a table that contains 2 columns ts (a timestamp) and dursec (a float - number of seconds) I want to insert the following into another table: the ts (timestamp column) and a second date which is ts + dursec I tried these select variations with no luck: select ts, ts + interval dursec seconds from tmp2 limit 1; select ts, ts + 'seconds' dursec from tmp2 limit 1; Anyone know the correct syntax for this ? Thanks in advance -- 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] pg_get_serial_sequence Strangeness/Unreliable?
Jaime Casanova wrote: can we make \d show if the sequence is owned by the table (ie: serial or manually created and owned) or is a manually created and maked default sequence? maybe a flag? My thought as well -- 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] Odd on-update inconsistency
Richard Huxton wrote: Steve Crawford wrote: I'm having trouble understanding something I saw in my data from yesterday involving an inconsistency between values in a table and its associated rule-updated log table. For application debugging purposes (effectiveness of web double-submit suppression) we have a rule that creates an entry in a log table whenever the table we are watching is updated: Ah, I think you'll find you don't. You have a rule that looks at first glance like it *should* add an entry to your log table. Rules rewrite the query like a macro would and OLD and NEW don't refer to a row but to the entire set of rows. The most common problems you'll see are related to: 1. nextval() / currval() not behaving like you'd think. 2. in particular with multiple-row updates or inserts See the mailing list archives for plenty of discussion, and I think the current manuals have a better description of rules than there used to be. For inserting to a log table you'll want a trigger. Hmmm. I was aware of certain issues with rules but in this case we have no sequences/nextval()/currval() issues and, except for period-start resets of certain columns, the normal update query only operates on a single row (increment count for a given location) - and I reverified that the key column really is unique. It's not a big problem (this project ends in a month anyway). I just want to increase my understanding to avoid future foot-gun potential as I hadn't seen how our current setup would cause this type of issue. I guess if it's critical that it works, I'll just write a trigger but rules are quicker and easier. Cheers, Steve -- 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] Date math question
Kevin Kempter wrote: Hi All; I have a table that contains 2 columns ts (a timestamp) and dursec (a float - number of seconds) I want to insert the following into another table: the ts (timestamp column) and a second date which is ts + dursec I tried these select variations with no luck: select ts, ts + interval dursec seconds from tmp2 limit 1; select ts, ts + 'seconds' dursec from tmp2 limit 1; Anyone know the correct syntax for this ? Try ts + dursec * '1 second'::interval Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Group by clause creating ERROR: wrong record type supplied in RETURN NEXT (version 8.1.11 -- grr...)
Hi all,
If I try to execute a dynamic query inside a function with a group by
statement, returning a setof, I get a weird error. It may be due to
the antiquated database version, but I would appreciate all the info I
can get (I tried looking in the PG bug tracker, but ... hahaha). If
it is as simple as upgrading, I will lean on the admin (I don't
control the box, or this wouldn't be an issue). I will try to
duplicate on a new machine later this week.
First the pass-through function (takes a sql statement, tries to clean
it, executes it):
create or replace function mkn.query_table_data (selectstring_p text)
returns setof record as $_$
DECLARE
outputrec_v record;
nasty_strings_re_v text;
rowcnt int := 0;
BEGIN
-- build regex from table of nasty strings
nasty_strings_re_v := (select
(array_to_string(array_accum(badword), '|')) from mkn.badwords);
raise debug '%', nasty_strings_re_v;
if (selectstring_p ~* nasty_strings_re_v) then -- bad stuff in query
raise exception 'Disallowed strings in query';
else -- get the records and return them
for outputrec_v in execute selectstring_p loop
rowcnt := rowcnt + 1;
return next outputrec_v;
end loop;
-- if no rows selected raise an exception (catch later)
if rowcnt = 0 then
raise exception 'Zero rows returned';
end if;
insert into mkn.custom_queries_log (query, output_rows,
error_code, error_msg)
values (selectstring_p,
rowcnt, NULL, NULL);
end if;
END;
$_$ LANGUAGE plpgsql;
-- Now a query that works OK being passed through this function
select * from mkn.query_table_data ('select p087001 as pop
from datatable_00041 order by pop desc limit 10')
as FOO (pop integer);
pop
--
3583
3555
3417
3410
3352
3133
3122
3013
2957
2941
(10 rows)
-- Now a query that fails being passed through the function
select * from mkn.query_table_data ('select sum(p087001) as pop
from datatable_00040 group by substr(geo_id, 13, 6) order by
pop desc limit 10')
as FOO (pop integer);
ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function query_table_data line 15 at return next
-- Now, what happens if I run the failing inside query directly from psql
select sum(p087001) as pop from datatable_00041 group by
substr(geo_id, 13, 6) order by pop desc limit 10;
pop
--
7498
7181
7130
7094
6879
6839
6677
6662
6632
6567
(10 rows)
-- Now, the version:
select version();
version
---
PostgreSQL 8.1.11 on x86_64-redhat-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)
(1 row)
-- thanks to everyone for their help, yet again!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] query evaluation
Hi all, I have the following query. select part_id, consume_part(part_id) from part where in_use = false; does it: a/ iterate over all parts, calling consume on them and then show the records where in_use is false (i.e. all parts have been consumed by the SP consume_part) or b/ filter the list using in_use = false and apply consume_part SP to those part_id's ? It is very _scary_ to imagine the first prospect, worse is if it did a mix of a/ and b/ ... where it does not apply consume_part on all parts but a partial set of parts that are exclusive of the set provided by SP, because then it would be unknown how the query worked (atleast if it consumed all parts then you could do something). -- :wq
Re: [GENERAL] Group by clause creating ERROR: wrong record type supplied in RETURN NEXT (version 8.1.11 -- grr...)
Webb Sprague wrote:
select * from mkn.query_table_data ('select sum(p087001) as pop
from datatable_00040 group by substr(geo_id, 13, 6) order by
pop desc limit 10')
as FOO (pop integer);
ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function query_table_data line 15 at return next
sum(int) returns bigint
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
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] query evaluation
On Wed, Nov 26, 2008 at 6:36 PM, Ravi Chemudugunta [EMAIL PROTECTED] wrote: Hi all, I have the following query. select part_id, consume_part(part_id) from part where in_use = false; does it: a/ iterate over all parts, calling consume on them and then show the records where in_use is false (i.e. all parts have been consumed by the SP consume_part) or b/ filter the list using in_use = false and apply consume_part SP to those part_id's ? It is very _scary_ to imagine the first prospect, worse is if it did a mix of a/ and b/ ... where it does not apply consume_part on all parts but a partial set of parts that are exclusive of the set provided by SP, because then it would be unknown how the query worked (atleast if it consumed all parts then you could do something). I'm quite sure it fires the where clause first, but to prove it to yourself, run it with explain analyze and look at the query plan. -- 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] [ADMIN] PgAgent Job Scehduler is NOT running
Thanks Vishal, I followed the document and now the statistics of the job is appearing and showing successful but actually backup is not made via this job. In output tab it gives the following error: could not open the file D:\xxx.backup:Permission denied. I tried to perform the task from both (normal postgres) users of OS. But no benefit. From: Vishal Arora [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Wednesday, November 26, 2008 10:25:11 PM Subject: RE: [ADMIN] PgAgent Job Scehduler is NOT running Try to follow the steps given in following link - http://wiki.postgresql.org/wiki/Image:PgAgent_for_windows.doc Date: Tue, 25 Nov 2008 02:32:46 -0800 From: [EMAIL PROTECTED] Subject: Re: [ADMIN] PgAgent Job Scehduler is NOT running To: [EMAIL PROTECTED]; [EMAIL PROTECTED] CC: pgsql-general@postgresql.org Service is running. After getting frustrated I dropped the pgagent schema and reworked. But the result is same. ???. Even entries in the concerned pga_tables is also very clear. Perhaps the trigger is not working . From: Vishal Arora [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 25, 2008 3:08:05 PM Subject: RE: [ADMIN] PgAgent Job Scehduler is NOT running Is the service running properly, try to schedule a new job and see the results. Date: Mon, 24 Nov 2008 23:23:15 -0800 From: [EMAIL PROTECTED] Subject: Re: [ADMIN] PgAgent Job Scehduler is NOT running To: [EMAIL PROTECTED]; [EMAIL PROTECTED] CC: pgsql-general@postgresql.org No error message appeared. Because NOstatistics is available for the job. It reflects that it is not running. - Forwarded Message From: Vishal Arora [EMAIL PROTECTED] To: [EMAIL PROTECTED]; pgsql-general@postgresql.org Cc: [EMAIL PROTECTED] Sent: Tuesday, November 25, 2008 11:47:33 AM Subject: RE: [ADMIN] PgAgent Job Scehduler is NOT running What is the error message you are getting. What is the interval you have set for scheduling the job. Date: Sun, 23 Nov 2008 23:51:46 -0800 From: [EMAIL PROTECTED] Subject: [ADMIN] PgAgent Job Scehduler is NOT running To: pgsql-general@postgresql.org CC: [EMAIL PROTECTED] Dear all, I installed PgAgent and started its service and successfully scheduled a backup and got 100% result. Now the same Job is not working even I reinstalled PgAgent but failed to get result. Regards, Abdul Rehman. Team India gets set to thwart Australia's quest for the final frontier. Catch the action on MSN Try it now! Calling TV buffs! Get TV listings, gossip on your fave stars and updates on hot new shows Try it now! Free up your senses. Experience reality up close on MSN video Try it!
