Re: [GENERAL] bytea encode performance issues
Tom Lane wrote:
Could we see EXPLAIN ANALYZE, not EXPLAIN? Without actual facts
to work from, any suggestions would be mere guesswork.
This was taken immediately after a vacuum analyze on the database.
HashAggregate (cost=41596.68..41596.84 rows=16 width=764) (actual
time=488263.802..488263.837 rows=40 loops=1)
- Nested Loop (cost=0.00..41596.60 rows=16 width=764) (actual
time=23375.445..488260.311 rows=40 loops=1)
- Nested Loop (cost=0.00..41463.32 rows=16 width=780)
(actual time=23375.344..488231.994 rows=40 loops=1)
- Seq Scan on dbmail_messageblks k
(cost=0.00..39193.21 rows=259 width=764) (actual time=30.662..486585.126
rows=2107 loops=1)
Filter: ((is_header = 0::smallint) AND
(encode(messageblk, 'escape'::text) ~~ '%Yossi%'::text))
- Index Scan using dbmail_messages_2 on dbmail_messages
m (cost=0.00..8.75 rows=1 width=16) (actual time=0.777..0.777 rows=0
loops=2107)
Index Cond: (m.physmessage_id = k.physmessage_id)
Filter: ((mailbox_idnr = 8) AND (status = ANY
('{0,1}'::integer[])))
- Index Scan using dbmail_physmessage_pkey on
dbmail_physmessage p (cost=0.00..8.32 rows=1 width=8) (actual
time=0.701..0.703 rows=1 loops=40)
Index Cond: (k.physmessage_id = p.id)
Total runtime: 488264.192 ms
Also, what can you tell us about the sizes of the messageblk
strings (max and avg would be interesting)?
select max(length(messageblk)),avg(length(messageblk)) from
dbmail_messageblks
MAXAVG
532259;48115.630147120314
--
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] recovery via base + WAL replay failure
Rob Adams wrote:
I'm trying to demonstrate recovery using the continuous archiving
backup technique. I'm using 8.3 on Windows.
I made a base backup while the postgres was running using the following
batch file:
--
psql -d test_database -U user_name -c SELECT pg_start_backup('test');
7za a -tzip C:\backup\base.zip C:\Program Files\PostgreSQL\8.3\data
psql -d test_database -U user_name -c SELECT pg_stop_backup();
--
No indication of any errors. However, I am unable to recover. Here are
my steps:
1. Stop service, replace the data directory w/ the base backup.
2. Create recovery.conf with this setting:
restore_command = 'copy C:\backup\%f %p'
3. Try to start service
After about 90 seconds, the dos prompt displays:
(postgres 8.3) service could not be started
The service did not report an error
Does anyone know what I am probably doing wrong?
You'll need to look at the PostgreSQL logs to see what they say.
But I'm curious about the 7za step. It gives no errors or warnings?
IIRC, you need to use VSS on Windows to avoid sharing violations when
opening the files unless a specific open mode is used. It could be that
7za uses that by default, but it's also possible tha tit's not actually
backing up all files...
//Magnus
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres-r patch: autoconf/make problem
hi, I am trying to compile the postgres-r patch, but ran into problems.. Probably just a simple lack of understanding of the make system. Any help is appreciated. I got the CVS head for postgres on Jul-31 and applying the Jul-31 patch from here: http://www.postgres-r.org/downloads/. The patch applies fine; no problems. When running autoconf it gives me the following warning when running ./configure --enable-replication once the configure scripts has ran: $ ./configure --enable-replication ... configure: WARNING: option ignored: --enable-replication $ Subsequent compilation by simply typing make seems *not* to compile anything in src/backend/replication (teh compilation as such goes through). When going directly to this directory and typing make, a few compilation errors appear for the file local.c. I attached the output at the end of this e-mail. But they might simply be caused by some compilation flags not correctly set due to earlier problems. I' sure it's just a simple problem me not specifying some command line option (compilation host is RHEL5). So in hope of a simple answer, this question: Am I missing some compilation options? Markus PS: the configure output: $ ./configure --enable-replication checking build system type... i686-pc-linux-gnu checking host system type... i686-pc-linux-gnu checking which template to use... linux checking whether to build with 64-bit integer date/time support... yes checking whether NLS is wanted... no checking for default port number... 5432 checking for block size... 8kB checking for segment size... 1GB checking for WAL block size... 8kB checking for WAL segment size... 16MB checking for gcc... gcc checking for C compiler default output file name... a.out checking whether the C compiler works... yes checking whether we are cross compiling... no checking for suffix of executables... checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking if gcc supports -Wdeclaration-after-statement... yes checking if gcc supports -Wendif-labels yes checking if gcc supports -fno-strict-aliasing... yes checking if gcc supports -fwrapv... yes checking whether the C compiler still works yes checking how to run the C preprocessor... gcc -E checking allow thread-safe client libraries... no checking whether to build with Tcl... no checking whether to build Perl modules... no checking whether to build Python modules... no checking whether to build with GSSAPI support... no checking whether to build with Kerberos 5 support... no checking whether to build with PAM support... no checking whether to build with LDAP support... no checking whether to build with Bonjour support... no checking whether to build with OpenSSL support... no checking for grep that handles long lines and -e... /bin/grep checking for egrep... /bin/grep -E checking for ld used by GCC... /usr/bin/ld checking if the linker (/usr/bin/ld) is GNU ld... yes checking for ranlib... ranlib checking for strip... strip checking whether it is possible to strip libraries... yes checking for tar... /bin/tar checking whether ln -s works... yes checking for gawk... gawk checking for bison... bison -y configure: using bison (GNU Bison) 2.3 checking for flex... /usr/bin/flex configure: using /usr/bin/flex version 2.5.4 checking for perl... /usr/bin/perl checking for main in -lm... yes checking for library containing setproctitle no checking for library containing dlopen... -ldl checking for library containing socket... none required checking for library containing shl_load... no checking for library containing getopt_long... none required checking for library containing crypt... -lcrypt checking for library containing fdatasync... none required checking for library containing shmget... none required checking for -lreadline... yes (-lreadline -ltermcap) checking for inflate in -lz... yes checking for ANSI C header files yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h yes checking for stdint.h... yes checking for unistd.h... yes checking crypt.h usability... yes checking crypt.h presence... yes checking for crypt.h... yes checking dld.h usability... no checking dld.h presence... no checking for dld.h... no checking fp_class.h usability... no checking fp_class.h presence... no checking for fp_class.h... no checking getopt.h usability... yes checking getopt.h presence... yes checking for getopt.h... yes checking ieeefp.h usability... no checking ieeefp.h presence... no checking for ieeefp.h... no checking langinfo.h usability... yes checking langinfo.h presence... yes checking for langinfo..h... yes checking poll.h usability... yes checking poll.h presence... yes checking for poll.h... yes checking pwd.h
Re: [GENERAL] bytea encode performance issues
On 2008-08-03 12:12, Sim Zacks wrote:
SELECT m.message_idnr,k.messageblk
FROM dbmail_messageblks k
JOIN dbmail_physmessage p ON k.physmessage_id = p.id
JOIN dbmail_messages m ON p.id = m.physmessage_id
WHERE
mailbox_idnr = 8
AND status IN (0,1 )
AND k.is_header = '0'
GROUP BY m.message_idnr,k.messageblk
HAVING ENCODE(k.messageblk::bytea,'escape') LIKE '%John%'
What is this encode() for? I think it is not needed and kills
performance, as it needs to copy every message body in memory, possibly
several times.
Why not just HAVING k.messageblk LIKE '%John%'?
Try this:
= \timing
= create temporary table test as
select
decode(
repeat(
'lorem ipsum dolor sit amet '
||s::text||E'\n'
,1000
),
'escape'
) as a
from generate_series(1,1) as s;
SELECT
Time: 10063.807 ms
= select count(*) from test where a like '%John%';
count
---
0
(1 row)
Time: 1280.973 ms
= select count(*) from test where encode(a,'escape') like '%John%';
count
---
0
(1 row)
Time: 5690.097 ms
Without encode search is 5 times faster. And for bigger bytea a
difference is even worse.
Even better:
= select count(*) from test where position('John' in a) != 0;
select count(*) from test where position('John' in a) != 0;
count
---
0
(1 row)
Time: 1098.768 ms
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Efficient data structures and UI for product matrix
Hi! We wish to provide our users with a simple-to-use web-based processor-selection tool, where a user could select a couple of attribute values and be presented with a list of matching processors. The basis of the required data would be provided by our editors as Excel documents of the following structure: attribute_1 attribute_2 ... processor_a some_value some_value ... processor_b some_value some_value ... This data would be normalized to the following structure on import: CREATE TABLE processors ( id serial NOT NULL, processor_name text NOT NULL, CONSTRAINT processors_pkey PRIMARY KEY (id) )WITHOUT OIDS; CREATE TABLE attributes ( id serial NOT NULL, attribute_name text NOT NULL, CONSTRAINT attributes_pkey PRIMARY KEY (id) )WITHOUT OIDS; CREATE TABLE processor_attributes ( processor_id integer NOT NULL, attribute_id integer NOT NULL, value_id integer NOT NULL, CONSTRAINT pk_processor_attributes PRIMARY KEY (processor_id, attribute_id, value_id), CONSTRAINT fk_processor_id FOREIGN KEY (processor_id) REFERENCES processors(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_attribute_id FOREIGN KEY (attribute_id) REFERENCES attributes(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_value_id FOREIGN KEY (value_id) REFERENCES attribute_values(id) )WITHOUT OIDS; CREATE TABLE attribute_values ( id serial NOT NULL, value text, attribute_id integer NOT NULL, CONSTRAINT attribute_values_pkey PRIMARY KEY (id), CONSTRAINT fk_attribute_id FOREIGN KEY (attribute_id) REFERENCES attributes(id) ON UPDATE CASCADE ON DELETE CASCADE )WITHOUT OIDS; The (web-based) UI should provide a dropdown field for each attribute (none selected per default) and a pageable table with the matching results underneath. The user should be kept from having to find out that there's no match for a selected combination of attribute-values, so after each selected dropdown, the as yet unselected dropdown-lists must be filtered to show only the still available attribute values - we intend to use some AJAX functions here. It'd be nice if the UI could be made fully dynamic, that's to say that it should reflect any changes to the number and names of attributes or their available values without any change to the application's code; the latter is in fact a must have, whereas the number and names of attributes would not change quite as frequently, so moderate changes to the code would be alright. Now, has anyone done anything similar recently and could provide some insight? I'd be particularly interested in any solutions involving some sort of de-normalization, views, procedures and suchlike to speed up performance of the drop-down-update process, especially as the number of attributes and the number of legal values for each attribute increases. Does anybody know of some sort of example application for this type of problem where we could find to inspiration? Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fwd: Returning Cursor
Hello, I am a developer working on postgres. I just wrote a function which ll return a refcurosor as shown below. CREATE OR REPLACE FUNCTION reffunc(refcursor) RETURNS refcursor AS $BODY$ BEGIN OPEN $1 FOR SELECT * FROM SAM1; RETURN $1; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; i have problems accessing this function from my middle tier i.e VC++. I wrote a VC statement to retrieve values from this refcursor using a record set. I cant access any of the values that the select statement in the function should retrieve.When we executed the above function from VC we only got the cursor name. We've been trying to access the values for the past one week. Can you please help me by sending me a sample code as to how to get the values in a recordset using this refcursor. Please do reply. This is very urgent. Thanks and regards Ravi Kiran L
[GENERAL] Fwd: Returning Cursor
Hello, I am a developer working on postgres. I just wrote a function which ll return a refcurosor as shown below. CREATE OR REPLACE FUNCTION reffunc(refcursor) RETURNS refcursor AS $BODY$ BEGIN OPEN $1 FOR SELECT * FROM SAM1; RETURN $1; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; i have problems accessing this function from my middle tier i.e VC++. I wrote a VC statement to retrieve values from this refcursor using a record set. I cant access any of the values that the select statement in the function should retrieve.When we executed the above function from VC we only got the cursor name. We've been trying to access the values for the past one week. Can you please help me by sending me a sample code as to how to get the values in a recordset using this refcursor. Please do reply. This is very urgent. Thanks and regards Ravi Kiran L
Re: [GENERAL] eliminating records not in (select id ... so SLOW?
On Fri, 01 Aug 2008 10:33:59 -0400 Tom Lane [EMAIL PROTECTED] wrote: Ivan Sergio Borgonovo [EMAIL PROTECTED] writes: Well I reached 3Gb of work_mem and still I got: Seq Scan on catalog_categoryitem (cost=31747.84..4019284477.13 rows=475532 width=6) Filter: (NOT (subplan)) SubPlan - Materialize (cost=31747.84..38509.51 rows=676167 width=8) - Seq Scan on catalog_items (cost=0.00..31071.67 rows=676167 width=8) Huh. The only way I can see for that to happen is if the datatypes involved aren't hashable. What's the datatypes of the two columns being compared, anyway? I changed both columns to bigint. I added 2 indexes on the ItemID column of both tables and increased work_mem to 3Gb [sic]. The query got executed in ~1300ms... but explain gave the same output as the one above. The problem is solved... but curious mind want to know. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] index speed and failed expectations?
sol= \d stats; Table public.stats Column| Type | Modifiers --++--- id | integer| not null start_time | timestamp(0) without time zone | not null ... Indexes: stats_start_time_idx btree (start_time) stats_id_key btree (id) There are roughly half a million rows. This query from the console: select * from stats order by start_time; takes 8 seconds before starting its output. Am I wrong in assuming that the index on start_time should make ORDER BY orders of magnitude faster? Or is this already fast enough? Or should I max up some memory (buffer) setting to achieve greater speeds? Not that the speed is crucial, just curious. TIA. -- 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] index speed and failed expectations?
This query from the console: select * from stats order by start_time; takes 8 seconds before starting its output. Am I wrong in assuming that the index on start_time should make ORDER BY orders of magnitude faster? Or is this already fast enough? Or should I max up some memory (buffer) setting to achieve greater speeds? Not that the speed is crucial, just curious. Postgresql won't use the index for queries like this. Due to the MVCC implementation, the index does not contain all necessary information and would therefore be slower than using the table data alone. (What postgresql lacks is a first_row/all_rows hint like oracle) However, if you limit the number of rows enough, you might force it to use an index: select * from stats order by start_time limit 1000; -- 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] index speed and failed expectations?
Adam Rich wrote: This query from the console: select * from stats order by start_time; takes 8 seconds before starting its output. Am I wrong in assuming that the index on start_time should make ORDER BY orders of magnitude faster? Or is this already fast enough? Or should I max up some memory (buffer) setting to achieve greater speeds? Not that the speed is crucial, just curious. Postgresql won't use the index for queries like this. Due to the MVCC implementation, the index does not contain all necessary information and would therefore be slower than using the table data alone. (What postgresql lacks is a first_row/all_rows hint like oracle) However, if you limit the number of rows enough, you might force it to use an index: select * from stats order by start_time limit 1000; Thanks! Since LIMIT/OFFSET is the typical usage pattern for a paginated data set accessed from the Web (which is my case), it immediately becomes a non-issue. -- 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] bytea encode performance issues
Sim Zacks [EMAIL PROTECTED] writes: Tom Lane wrote: Could we see EXPLAIN ANALYZE, not EXPLAIN? Without actual facts to work from, any suggestions would be mere guesswork. - Seq Scan on dbmail_messageblks k (cost=0.00..39193.21 rows=259 width=764) (actual time=30.662..486585.126 rows=2107 loops=1) Filter: ((is_header = 0::smallint) AND (encode(messageblk, 'escape'::text) ~~ '%Yossi%'::text)) okay, the time really is being spent in the seqscan ... Also, what can you tell us about the sizes of the messageblk strings (max and avg would be interesting)? select max(length(messageblk)),avg(length(messageblk)) from dbmail_messageblks MAXAVG 532259;48115.630147120314 ... but given that, I wonder whether the cost isn't from fetching the toasted messageblk data, and nothing directly to do with either the encode() call or the ~~ test. It would be interesting to compare the results of explain analyze select encode(messageblk, 'escape') ~~ '%Yossi%' from dbmail_messageblks where is_header = 0; explain analyze select encode(messageblk, 'escape') from dbmail_messageblks where is_header = 0; explain analyze select messageblk = 'X' from dbmail_messageblks where is_header = 0; explain analyze select length(messageblk) from dbmail_messageblks where is_header = 0; (length is chosen with malice aforethought: unlike the other cases, it doesn't require detoasting a toasted input) 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] index speed and failed expectations?
Adam Rich [EMAIL PROTECTED] writes: This query from the console: select * from stats order by start_time; takes 8 seconds before starting its output. Am I wrong in assuming that the index on start_time should make ORDER BY orders of magnitude faster? Postgresql won't use the index for queries like this. won't - might not. It all depends on the relative cost estimates for indexscan vs seqscan + sort. For a large table it's quite likely that the latter will be cheaper, because it has a better-localized access pattern. (What postgresql lacks is a first_row/all_rows hint like oracle) That's spelled LIMIT ;-). Also, you can bias the choice in favor of a fast-start plan if you use a cursor rather than a plain SELECT. In that case the planner makes some allowance for the idea that you might not want all the rows, or might be more interested in getting the first ones quickly than minimizing the total time to fetch all the rows. 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] index speed and failed expectations?
However, if you limit the number of rows enough, you might force it to use an index: select * from stats order by start_time limit 1000; Thanks! Since LIMIT/OFFSET is the typical usage pattern for a paginated data set accessed from the Web (which is my case), it immediately becomes a non-issue. We do a lot of queries with order by limit n, and from my experience setting enable_sort to off on the database also makes a massive difference. http://www.postgresql.org/docs/8.3/static/indexes-ordering.html __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- 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] index speed and failed expectations?
On Mon, Aug 04, 2008 at 08:35:28AM -0500, Adam Rich wrote: This query from the console: select * from stats order by start_time; takes 8 seconds before starting its output. Am I wrong in assuming that the index on start_time should make ORDER BY orders of magnitude faster? Postgresql won't use the index for queries like this. Due to the MVCC implementation, the index does not contain all necessary information and would therefore be slower than using the table data alone. Not necessarily true. Despite the index not having enough information, the planner might still decide that using the index would be faster than executing a sort. create table stats ( id serial primary key, start_time timestamp with time zone not null ); insert into stats (start_time) select now() - random() * '1 year'::interval from generate_series(1, 10); create index stats_start_time_idx on stats (start_time); analyze stats; explain analyze select * from stats order by start_time; QUERY PLAN Index Scan using stats_start_time_idx on stats (cost=0.00..4767.83 rows=10 width=12) (actual time=0.146..994.674 rows=10 loops=1) Total runtime: 1419.943 ms (2 rows) set enable_indexscan to off; explain analyze select * from stats order by start_time; QUERY PLAN - Sort (cost=9845.82..10095.82 rows=10 width=12) (actual time=3240.976..3800.038 rows=10 loops=1) Sort Key: start_time - Seq Scan on stats (cost=0.00..1541.00 rows=10 width=12) (actual time=0.091..500.853 rows=10 loops=1) Total runtime: 4226.870 ms (4 rows) -- Michael Fuhr -- 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 duplicate lines but save one of the lines?
Julio Cesar Sánchez González wrote:
A B wrote:
I have a table with rows like this
A 1
A 1
B 3
B 3
C 44
C 44
and so on.
and I want it to be
A 1
B 3
C 44
so how can I remove the all the duplicate lines but one?
You think this would help?
create table temp(text varchar(20),id integer );
INSERT INTO temp values('A',10);
INSERT INTO temp values('A',10);
INSERT INTO temp values('B',20);
INSERT INTO temp values('B',20);
INSERT INTO temp values('B',20);
select * from temp;
text | id
--+
A| 10
A| 10
B| 20
B| 20
B| 20
select text,id, count(1) from temp group by 1,2;
text | id | count
--++---
A| 10 | 2
B| 20 | 3
and forget about the count from the result set.
--
Thanks Regards
Kedar Parikh
Netcore Solutions Pvt. Ltd.
Tel: +91 (22) 6662 8135
Mob: +91 9819634734
Email: [EMAIL PROTECTED]
Web: www.netcore.co.in
===
sms START NEWS your city to 09845398453 for Breaking News and Top
Stories on Business, Sports Politics. For more services visit
http://www.mytodaysms.com
===
[GENERAL] Howto disable login?
Hi all, is there some way to disable and enable login in PostgreSQL? I would like to have only local login enabled when the server starts and make some checks before allowing general login. Best regards and thanks, Teemu Juntunen
Re: [GENERAL] recovery via base + WAL replay failure
On Sun, 3 Aug 2008, Rob Adams wrote:
I made a base backup while the postgres was running using the following batch
file:
psql -d test_database -U user_name -c SELECT pg_start_backup('test');
What did you have archive_command set to? That needs to dump the WAL
files generated while the backup is going on somewhere that gets copied
over after the main copy is done, and you need the last of them referenced
by the backup copied over before you can use that backup. Steps (1) and
(5) of
http://www.postgresql.org/docs/current/static/continuous-archiving.html
are the hard parts here and I don't see that you're addressing them so
far, and that will keep the copy from starting if all the files aren't
there.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] is a 'pairwise' possible / feasible in SQL?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, I have a table of the form aid cid - - 1 123 2 456 3 667 3 879 3 123 4 878 4 456 4 123 5 999 5 667 5 879 My goal is to identify for each pair of cid values, the number of times they have the same aid Thus for example I would have paircount - - 123 456 1 667 879 2 ... I currently do this by using a Python script to do a pairwise lookup, as select count(aid) where cid = 123 and cid = 456; but I was wondering whether I could construct a single SQL statement to do this. Any pointers would be appreciated, Thanks, - --- Rajarshi Guha [EMAIL PROTECTED] GPG Fingerprint: D070 5427 CC5B 7938 929C DD13 66A1 922C 51E7 9E84 - --- All great discoveries are made by mistake. -- Young -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.8 (Darwin) iEYEARECAAYFAkiXRYUACgkQZqGSLFHnnoTJJQCgtvromGcYfQVGsekGFQJU6vTo oHgAnjpfKSkZR0MqBjdE6WFGO8SBr2WH =zZJk -END PGP SIGNATURE- -- 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] recovery via base + WAL replay failure
what error show the log file?
--- On Mon, 8/4/08, Greg Smith [EMAIL PROTECTED] wrote:
From: Greg Smith [EMAIL PROTECTED]
Subject: Re: [GENERAL] recovery via base + WAL replay failure
To: Rob Adams [EMAIL PROTECTED]
Cc: postgres general pgsql-general@postgresql.org
Date: Monday, August 4, 2008, 5:58 PM
On Sun, 3 Aug 2008, Rob Adams wrote:
I made a base backup while the postgres was running
using the following batch
file:
psql -d test_database -U user_name -c SELECT
pg_start_backup('test');
What did you have archive_command set to? That needs to
dump the WAL
files generated while the backup is going on somewhere that
gets copied
over after the main copy is done, and you need the last of
them referenced
by the backup copied over before you can use that backup.
Steps (1) and
(5) of
http://www.postgresql.org/docs/current/static/continuous-archiving.html
are the hard parts here and I don't see that you're
addressing them so
far, and that will keep the copy from starting if all the
files aren't
there.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com
Baltimore, MD
--
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] recovery via base + WAL replay failure
The WAL file archiving appears to be working correctly. These are the
settings I'm using for archiving the WAL files:
archive_mode = on
archive_command = 'copy %p C:\backup\%f /A'
archive_timeout = 15s
Thanks again,
--Rob
Greg Smith wrote:
On Sun, 3 Aug 2008, Rob Adams wrote:
I made a base backup while the postgres was running using the
following batch file:
psql -d test_database -U user_name -c SELECT pg_start_backup('test');
What did you have archive_command set to? That needs to dump the WAL
files generated while the backup is going on somewhere that gets copied
over after the main copy is done, and you need the last of them
referenced by the backup copied over before you can use that backup.
Steps (1) and (5) of
http://www.postgresql.org/docs/current/static/continuous-archiving.html
are the hard parts here and I don't see that you're addressing them so
far, and that will keep the copy from starting if all the files aren't
there.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
--
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] Howto disable login?
You can using the pg_hba.conf file. Set the non-local accounts to reject when you start the database. After you finish your scripts, change the pg_hba.conf file to enable logins and then use pg_ctl reload to enable the new pg_hba.conf file. host all all 0.0.0.0/0 reject And then change it to: host all all 0.0.0.0/0 md5 Jon From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Teemu Juntunen Sent: Monday, August 04, 2008 12:20 PM To: PostgreSQL Subject: [GENERAL] Howto disable login? Hi all, is there some way to disable and enable login in PostgreSQL? I would like to have only local login enabled when the server starts and make some checks before allowing general login. Best regards and thanks, Teemu Juntunen
Re: [GENERAL] [EMAIL PROTECTED]
Hiroshi-san,
Is this something specific to windows? If so, should this be consider a bug?
Robert Treat
On Sunday 03 August 2008 18:01:05 Hiroshi Saito wrote:
Hi.
Sorry, it was not included in release.
please see,
http://winpg.jp/~saito/pg_work/OSSP_win32/
Regards,
Hiroshi Saito
Hi all,
I installed postgresql-8.3.3-1 for win2000 with UUID-OSSP. Following
the documentation I issued this query:
SELECT uuid_generate_v3(uuid_ns_url(), 'http://www.postgresql.org');
but the DB returns with this error message:
ERROR: function uuid_ns_url() 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: 25
Any hint to use UUID within my database tirggers?
Thank you,
Laci
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
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] Howto disable login?
Teemufollow the instructions for implementing SSL in postgreshttp://developer.postgresql.org/pgdocs/postgres/ssl-tcp.htmlbut you'll have to start by compiling in the SSL module into postgres build http://developer.postgresql.org/pgdocs/postgres/install-procedure.html./configure --with-openssl kiittääMartin__ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED]: [EMAIL PROTECTED]: [GENERAL] Howto disable login?Date: Mon, 4 Aug 2008 20:19:42 +0300 Hi all, is there some way to disable and enable login in PostgreSQL? I would like to have only local login enabled when the server starts and make some checks before allowing general login. Best regards and thanks, Teemu Juntunen _ Get Windows Live and get whatever you need, wherever you are. Start here. http://www.windowslive.com/default.html?ocid=TXT_TAGLM_WL_Home_082008
Re: [GENERAL] is a 'pairwise' possible / feasible in SQL?
On Mon, Aug 4, 2008 at 2:08 PM, Rajarshi Guha [EMAIL PROTECTED] wrote: select count(aid) where cid = 123 and cid = 456; but I was wondering whether I could construct a single SQL statement to do this. Any pointers would be appreciated, Typed into gmail, so may need some tweaking, but something to the effect of: select count(*) from table a inner join table b on a.aid=b.bid group by a.cid,b.cid; should do the trick, I'd think... -- - David T. Wilson [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] is a 'pairwise' possible / feasible in SQL?
On Mon, Aug 4, 2008 at 1:02 PM, David Wilson [EMAIL PROTECTED] wrote: On Mon, Aug 4, 2008 at 2:08 PM, Rajarshi Guha [EMAIL PROTECTED] wrote: select count(aid) where cid = 123 and cid = 456; but I was wondering whether I could construct a single SQL statement to do this. Any pointers would be appreciated, Typed into gmail, so may need some tweaking, but something to the effect of: select count(*) from table a inner join table b on a.aid=b.bid group by a.cid,b.cid; should do the trick, I'd think... But then you need remove the dups where you got: 667 999 2 999 667 2 -- 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] recovery via base + WAL replay failure
There is nothing in the log file (in pg_log dir) with regard to this.
Should I set any particular parameter in the postgresql.conf file to log
information about a failed startup? I have not altered or uncommented
any lines in the ERROR REPORTING AND LOGGING section of the conf file.
Thanks again,
--Rob Adams
Lennin Caro wrote:
what error show the log file?
--- On Mon, 8/4/08, Greg Smith [EMAIL PROTECTED] wrote:
From: Greg Smith [EMAIL PROTECTED]
Subject: Re: [GENERAL] recovery via base + WAL replay failure
To: Rob Adams [EMAIL PROTECTED]
Cc: postgres general pgsql-general@postgresql.org
Date: Monday, August 4, 2008, 5:58 PM
On Sun, 3 Aug 2008, Rob Adams wrote:
I made a base backup while the postgres was running
using the following batch
file:
psql -d test_database -U user_name -c SELECT
pg_start_backup('test');
What did you have archive_command set to? That needs to
dump the WAL
files generated while the backup is going on somewhere that
gets copied
over after the main copy is done, and you need the last of
them referenced
by the backup copied over before you can use that backup.
Steps (1) and
(5) of
http://www.postgresql.org/docs/current/static/continuous-archiving.html
are the hard parts here and I don't see that you're
addressing them so
far, and that will keep the copy from starting if all the
files aren't
there.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com
Baltimore, MD
--
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] is a 'pairwise' possible / feasible in SQL?
On 2:08 pm 08/04/08 Rajarshi Guha [EMAIL PROTECTED] wrote: paircount - - 123 456 1 667 879 2 create temp table aic_cid ( id smallint, cid smallint ); insert into aic_cid values (1,123); insert into aic_cid values (2,456); insert into aic_cid values (3,667); insert into aic_cid values (3,879); insert into aic_cid values (3,123); insert into aic_cid values (4,878); insert into aic_cid values (4,456); insert into aic_cid values (4,123); insert into aic_cid values (5,999); insert into aic_cid values (5,667); insert into aic_cid values (5,879); select a.cid as ac, b.cid as bc, count(*) from aic_cid a left outer join aic_cid b on a.cid b.cid and a.id = b.id where b.cid is not null group by a.cid, b.cid order by a.cid; ac | bc | count -+-+--- 123 | 456 | 1 123 | 667 | 1 123 | 878 | 1 123 | 879 | 1 456 | 123 | 1 456 | 878 | 1 667 | 123 | 1 667 | 879 | 2 667 | 999 | 1 878 | 123 | 1 878 | 456 | 1 879 | 123 | 1 879 | 667 | 2 879 | 999 | 1 999 | 667 | 1 999 | 879 | 1 Is that what you are looking for? -- 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] is a 'pairwise' possible / feasible in SQL?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Aug 4, 2008, at 4:55 PM, Francisco Reyes wrote: On 2:08 pm 08/04/08 Rajarshi Guha [EMAIL PROTECTED] wrote: paircount - - 123 456 1 667 879 2 snip select a.cid as ac, b.cid as bc, count(*) from aic_cid a left outer join aic_cid b on a.cid b.cid and a.id = b.id where b.cid is not null group by a.cid, b.cid order by a.cid; ac | bc | count -+-+--- 123 | 456 | 1 123 | 667 | 1 123 | 878 | 1 123 | 879 | 1 456 | 123 | 1 456 | 878 | 1 667 | 123 | 1 667 | 879 | 2 667 | 999 | 1 878 | 123 | 1 878 | 456 | 1 879 | 123 | 1 879 | 667 | 2 879 | 999 | 1 999 | 667 | 1 999 | 879 | 1 Is that what you are looking for? Thanks a lot - this is very close. Ideally, I'd want unique pairs, so the row 879 | 999 | 1 is the same as 999 | 879 | 1 Can these duplicates be avoided? - --- Rajarshi Guha [EMAIL PROTECTED] GPG Fingerprint: D070 5427 CC5B 7938 929C DD13 66A1 922C 51E7 9E84 - --- How I wish I were what I was when I wished I were what I am. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.8 (Darwin) iEYEARECAAYFAkiXbe8ACgkQZqGSLFHnnoRXPACeMcPqXG4QIf308ufnAHev9hlG EEoAoLzU5tmL1ipiUIp69N9mOvnsfrES =JOg1 -END PGP SIGNATURE- -- 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] is a 'pairwise' possible / feasible in SQL?
On mán, 2008-08-04 at 17:00 -0400, Rajarshi Guha wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Aug 4, 2008, at 4:55 PM, Francisco Reyes wrote: On 2:08 pm 08/04/08 Rajarshi Guha [EMAIL PROTECTED] wrote: paircount - - 123 456 1 667 879 2 snip select a.cid as ac, b.cid as bc, count(*) from aic_cid a left outer join aic_cid b on a.cid b.cid and a.id = b.id where b.cid is not null group by a.cid, b.cid order by a.cid; ac | bc | count -+-+--- 123 | 456 | 1 123 | 667 | 1 ... Is that what you are looking for? Thanks a lot - this is very close. Ideally, I'd want unique pairs, so the row 879 | 999 | 1 is the same as 999 | 879 | 1 Can these duplicates be avoided? just add a acbc condition: select a.cid as ac, b.cid as bc, count(*) from aic_cid a left outer join aic_cid b on a.cid b.cid and a.id = b.id where b.cid is not null AND a.cid b.cid group by a.cid, b.cid order by a.cid; gnari -- 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] is a 'pairwise' possible / feasible in SQL?
Rajarshi Guha wrote On Aug 4, 2008, at 4:55 PM, Francisco Reyes wrote: On 2:08 pm 08/04/08 Rajarshi Guha [EMAIL PROTECTED] wrote: paircount - - 123 456 1 667 879 2 snip select a.cid as ac, b.cid as bc, count(*) from aic_cid a left outer join aic_cid b on a.cid b.cid and a.id = b.id where b.cid is not null group by a.cid, b.cid order by a.cid; ac | bc | count -+-+--- 123 | 456 | 1 123 | 667 | 1 123 | 878 | 1 123 | 879 | 1 456 | 123 | 1 456 | 878 | 1 667 | 123 | 1 667 | 879 | 2 667 | 999 | 1 878 | 123 | 1 878 | 456 | 1 879 | 123 | 1 879 | 667 | 2 879 | 999 | 1 999 | 667 | 1 999 | 879 | 1 Is that what you are looking for? Thanks a lot - this is very close. Ideally, I'd want unique pairs, so the row 879 | 999 | 1 is the same as 999 | 879 | 1 Can these duplicates be avoided? Depends on values and other distinguishing attributes For the given example - assuming pairing of a given cid with itself is not to be expected: add a and a.cid b.cid to the query Rainer --- Rajarshi Guha [EMAIL PROTECTED] GPG Fingerprint: D070 5427 CC5B 7938 929C DD13 66A1 922C 51E7 9E84 --- How I wish I were what I was when I wished I were what I am. -- 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] Initdb problem on debian mips cobalt: Bus error
Heres a backtrace on a fresh core file http://privatepaste.com/911BTjYrY1 Does this change get us any closer? --- On Tue, 22/7/08, Glyn Astill [EMAIL PROTECTED] wrote: From: Glyn Astill [EMAIL PROTECTED] Subject: Re: [GENERAL] Initdb problem on debian mips cobalt: Bus error To: Tom Lane [EMAIL PROTECTED] Cc: Stefan Kaltenbrunner [EMAIL PROTECTED], pgsql-general@postgresql.org Date: Tuesday, 22 July, 2008, 7:35 PM The stack size rlimit looks normal, which makes a crash in this spot look even less probable. I think maybe you are looking at a stale corefile that doesn't quite correspond to this postgres executable. You are correct. I just checked and the core file was created on the 18th, that must be from the first attempt to run make check. I just assumed that the next time I attempted to run make check it'd be overwriting it, and that's obviously not the case. I'll try and get it to generate a fresh file. __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- 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] [EMAIL PROTECTED]
Hi Robert-san
Ahh yes..
I'm sorry delaying release bugfix was not included in pg8.3.3.
Then,I will adjust next release(1.6.3/4) with Rarf-san again.
Regards,
Hiroshi Saito.
Hiroshi-san,
Is this something specific to windows? If so, should this be consider a bug?
Robert Treat
On Sunday 03 August 2008 18:01:05 Hiroshi Saito wrote:
Hi.
Sorry, it was not included in release.
please see,
http://winpg.jp/~saito/pg_work/OSSP_win32/
Regards,
Hiroshi Saito
Hi all,
I installed postgresql-8.3.3-1 for win2000 with UUID-OSSP. Following
the documentation I issued this query:
SELECT uuid_generate_v3(uuid_ns_url(), 'http://www.postgresql.org');
but the DB returns with this error message:
ERROR: function uuid_ns_url() 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: 25
Any hint to use UUID within my database tirggers?
Thank you,
Laci
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
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] recovery via base + WAL replay failure
I found error log entries in the Windows Event Viewer:
2008-08-01 23:57:55 GMT FATAL: could not remove old lock file
postmaster.pid: Permission denied
2008-08-01 23:57:55 GMT HINT: The file seems accidentally left over,
but it could not be removed. Please remove the file by hand and try again.
However, there is no postmaster.pid file in the data directory. (I
can't find one anywhere else, either.)
Any ideas?
Thanks,
--Rob Adams
Lennin Caro wrote:
what error show the log file?
--- On Mon, 8/4/08, Greg Smith [EMAIL PROTECTED] wrote:
From: Greg Smith [EMAIL PROTECTED]
Subject: Re: [GENERAL] recovery via base + WAL replay failure
To: Rob Adams [EMAIL PROTECTED]
Cc: postgres general pgsql-general@postgresql.org
Date: Monday, August 4, 2008, 5:58 PM
On Sun, 3 Aug 2008, Rob Adams wrote:
I made a base backup while the postgres was running
using the following batch
file:
psql -d test_database -U user_name -c SELECT
pg_start_backup('test');
What did you have archive_command set to? That needs to
dump the WAL
files generated while the backup is going on somewhere that
gets copied
over after the main copy is done, and you need the last of
them referenced
by the backup copied over before you can use that backup.
Steps (1) and
(5) of
http://www.postgresql.org/docs/current/static/continuous-archiving.html
are the hard parts here and I don't see that you're
addressing them so
far, and that will keep the copy from starting if all the
files aren't
there.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com
Baltimore, MD
--
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] Initdb problem on debian mips cobalt: Bus error
Glyn Astill [EMAIL PROTECTED] writes: Heres a backtrace on a fresh core file http://privatepaste.com/911BTjYrY1 Does this change get us any closer? Not really ... there's no plausible reason to crash there, either. Just for entertainment's sake, try recompiling with -O0 instead of the default -O2. (Rather than trying to browbeat configure into doing this, I'd suggest manually adjusting CFLAGS in src/Makefile.global, then make clean and rebuild.) This has two purposes: if it's a compiler bug, that will likely make it go away; and if gdb is misleading us about exactly where the crash is, that should help it give a correct answer. 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] Initdb problem on debian mips cobalt: Bus error
Tom Lane [EMAIL PROTECTED] writes: (Rather than trying to browbeat configure into doing this, I'd suggest manually adjusting CFLAGS in src/Makefile.global, then make clean and rebuild.) eh? either of these should work fine: ./configure --enable-debug CFLAGS=-O0 CFLAGS=-O0 ./configure --enable-debug And yes, you have to do make clean. I often forget that step :( -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- 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] Initdb problem on debian mips cobalt: Bus error
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: (Rather than trying to browbeat configure into doing this, I'd suggest manually adjusting CFLAGS in src/Makefile.global, then make clean and rebuild.) eh? either of these should work fine: ./configure --enable-debug CFLAGS=-O0 CFLAGS=-O0 ./configure --enable-debug The trouble with that approach is that it overrides *everything* that configure would normally put into CFLAGS. I only want one thing changing, please ... this is confusing enough already. 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] Initdb problem on debian mips cobalt: Bus error
Gregory Stark wrote: Tom Lane [EMAIL PROTECTED] writes: (Rather than trying to browbeat configure into doing this, I'd suggest manually adjusting CFLAGS in src/Makefile.global, then make clean and rebuild.) eh? either of these should work fine: ./configure --enable-debug CFLAGS=-O0 CFLAGS=-O0 ./configure --enable-debug And yes, you have to do make clean. I often forget that step :( I find it easier to create a src/Makefile.custom containing the following line: CFLAGS := $(patsubst -O2,-O0,$(CFLAGS)) When I'm done I just rename the file away to keep it around for next time. -- 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] Fwd: Returning Cursor
ravi kiran wrote: Hello, I am a developer working on postgres. I just wrote a function which ll return a refcurosor as shown below. [snip] i have problems accessing this function from my middle tier i.e VC++. I wrote a VC statement to retrieve values from this refcursor using a record set. Please show us your code. Also, what access method are you using? VC isn't a database access method. Are you using libpq? ODBC? LINQ? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Easy way to alias all columns in a table by adding a prefix or suffix?
All, I have 2 tables with a lot of columns with similar names. I'd like to join both tables and include all columns from each without naming collisions. I can use the 'AS' to rename a single column, but is there a way to do the rename in bulk by just appending a prefix or suffix to the column names from each respective table? I want to do something like this: SELECT a.* AS prefix1_*, b.* AS prefix2_* FROM a, b WHERE a.id = b.id AND a.id = 123; The result would be to select all columns from a but rename each to have prefix1_ appended to the front. a.id - prefix1_id b.id - prefix2_id a.xpos - prefix1_xpos b.xpos - prefix2_xpos a.ypos - prefix1_ypos b.ypos - prefix2_ypos etc... Does this request make sense? Does something like this exist? I don't really NEED to have this, I'm just trying to be lazy. -- Dante -- 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] bytea encode performance issues
Results below: ... but given that, I wonder whether the cost isn't from fetching the toasted messageblk data, and nothing directly to do with either the encode() call or the ~~ test. It would be interesting to compare the results of explain analyze select encode(messageblk, 'escape') ~~ '%Yossi%' from dbmail_messageblks where is_header = 0; Seq Scan on dbmail_messageblks (cost=0.00..38449.06 rows=162096 width=756) (actual time=0.071..492776.008 rows=166748 loops=1) Filter: (is_header = 0) Total runtime: 492988.410 ms explain analyze select encode(messageblk, 'escape') from dbmail_messageblks where is_header = 0; Seq Scan on dbmail_messageblks (cost=0.00..38043.81 rows=162096 width=756) (actual time=16.008..306408.633 rows=166750 loops=1) Filter: (is_header = 0) Total runtime: 306585.369 ms explain analyze select messageblk = 'X' from dbmail_messageblks where is_header = 0; Seq Scan on dbmail_messageblks (cost=0.00..38043.81 rows=162096 width=756) (actual time=18.169..251212.223 rows=166754 loops=1) Filter: (is_header = 0) Total runtime: 251384.900 ms explain analyze select length(messageblk) from dbmail_messageblks where is_header = 0; Seq Scan on dbmail_messageblks (cost=0.00..38043.81 rows=162096 width=756) (actual time=20.436..2585.098 rows=166757 loops=1) Filter: (is_header = 0) Total runtime: 2673.840 ms (length is chosen with malice aforethought: unlike the other cases, it doesn't require detoasting a toasted input) 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
