You can do it in straight sql like so.
SELECT (array[col1, col2, col3, col4, col5, col6, col7, col8, col9, col10])[i]
FROM test t, generate_series(1,10) i
Art
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
On Wed, Jul 23, 2008 at 01:15:30PM +1000, Chris wrote:
Now *any* error inside transaction will trigger auto rollback for
*all* inserts so I don't need to explicitly issue conditional
rollback? Also is begin/commit transaction == start/end
transaction??
What if something gets an invalid
Is it safe to use plpgsql functions with 'security definer'?
For example we have table for spamassassin preferences, and user spamassassin.
I don't want spamassassin user to see user database (passwords, ...)
So I use function:
CREATE OR REPLACE FUNCTION get_sa_preferences( VARCHAR )
RETURNS
Hi!
We have been using postgresql since a while without problems. But now I find
we experience some slowness and the weird thing is that it seems to happen
because of a very short table (less than 200 lines), called lockers (see
below it's structure).
This table is accessed very often by a lot
Steve Martin wrote:
I am trying to create a PL/PGSQL function to return the values of the
fields in a record, e.g. 1 value per row in the output of the function.
How do you substitute a variable?
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE
ted varchar;
Guillaume Bog wrote:
It seems I'm a bit stuck here. I'd appreciate some help. My main general
question is how to handle very small but hot status table that has to be
updated every 30 seconds by 100 different persons, read and updated from
many sides, and also joined with some more common tables
I got some lint/splint output off-list (thanks!) but couldn't
find anything with related to the problem.
I did find (and fixed) an unrelated error in the python version
that prevented it to work on the annonynized input files though.
Anyone other ideas on this problem or what can be done to
I've worked as a web developer on mostly small business websites for the
past seven years, and while I've had some limited experience with older
versions of PostgreSQL (7.* ??), I've mostly used MySQL all this time.
I now work for local govt and am building a large intranet-like system
which
On 23/07/2008 10:48, admin wrote:
So anyway, life story aside, I have a couple of very newbie questions
after tinkering with PostgreSQL 8.1.9 for a day converting some
PHP/MySQL code:
Hi there,
You should consider upgrading to 8.3 if you can - there are significant
performance improvements.
On 23/07/2008 11:01, Raymond O'Donnell wrote:
On 23/07/2008 10:48, admin wrote:
1. Is a SEQUENCE what I use instead of auto_increment?
Yes. The easiest thing is to define the column as type SERIAL - this
will create the sequence for you and associate it with the column.
Alternatively, you
admin wrote:
I'm convinced that PostgreSQL's performance is not an issue (both
because it's improved and traffic will be relatively low anyway)
It's really rather solid in performance terms anyway, especially for
non-trivial workloads where data consistency and reliability are important.
On Wed, Jul 23, 2008 at 07:18:15PM +0930, admin wrote:
1. Is a SEQUENCE what I use instead of auto_increment?
Yes. Perhaps better use it indirectly with (BIG)SERIAL:
create table foo (
pk (big)serial
);
2. Does this work in PostgreSQL:
INSERT INTO table VALUES ('x','y','z')
Yes,
am Wed, dem 23.07.2008, um 19:18:15 +0930 mailte admin folgendes:
1. Is a SEQUENCE what I use instead of auto_increment?
Yes.
2. Does this work in PostgreSQL:
INSERT INTO table VALUES ('x','y','z')
or do I need to do this
INSERT INTO table (fld_x,fld_y,fld_z) VALUES ('x','y','z')
On Wed, Jul 23, 2008 at 4:50 PM, Richard Huxton [EMAIL PROTECTED] wrote:
Guillaume Bog wrote:
It seems I'm a bit stuck here. I'd appreciate some help. My main general
question is how to handle very small but hot status table that has to be
updated every 30 seconds by 100 different persons,
admin wrote:
So anyway, life story aside, I have a couple of very newbie questions
after tinkering with PostgreSQL 8.1.9 for a day converting some
PHP/MySQL code:
Here I have to ask the obvious thing: Why not a more current version?
1. Is a SEQUENCE what I use instead of auto_increment?
On Wed, Jul 23, 2008 at 3:48 AM, admin [EMAIL PROTECTED] wrote:
I'm convinced that PostgreSQL's performance is not an issue (both because
it's improved and traffic will be relatively low anyway), and that the
benefits of PostgreSQL's advanced features are too good to ignore. I'm
hoping to
Thanks Chris and Karsten. I still don't quite understand why invalid
state/record-mismatch would also not trigger auto rollback. How can I
even include something *outside* a transaction *inside* it- shouldn't
everything between begin and end be subject to auto rollback no
matter what?
Also what
The data set I am working with has a very uneven distribution.
I had to to set random_page_cost = 0.75 to get good plans.
However, that first tries bitmap scans which perform very poorly.
Is there a way to have the planner to favor index scans and disfavor bitmap
scans? Is my best choice to just
Raymond O'Donnell wrote:
1. Is a SEQUENCE what I use instead of auto_increment?
Yes. The easiest thing is to define the column as type SERIAL - this
will create the sequence for you and associate it with the column.
Alternatively, you can create the sequence by hand, create the column
as an
Craig Ringer wrote:
INSERT INTO table (fld_y,fld_z) VALUES ('y','z')
which is really doing:
INSERT INTO table (fld_x, fld_y,fld_z) VALUES (DEFAULT, 'y','z')
To be honest I hadn't seen the use of INSERT INTO table (fld_x,
fld_y,fld_z) VALUES (DEFAULT, 'y','z') before, I have always gone
Francisco Reyes [EMAIL PROTECTED] writes:
So far in this data set almost every time bitmap scans are used the queries
do worse, much worse. I had one extreme case where a sequential scan would
finish in 20 minutes and the same query using bitmap scans would take over
a day to finish.
That's
Shane Ambler wrote:
INSERT INTO table (fld_x, fld_y,fld_z) VALUES (DEFAULT, 'y','z')
To be honest I hadn't seen the use of INSERT INTO table (fld_x,
fld_y,fld_z) VALUES (DEFAULT, 'y','z') before, I have always gone with
INSERT INTO table (fld_x, fld_y,fld_z) VALUES (NULL, 'y','z')
...
On Wed, Jul 23, 2008 at 4:08 AM, Klint Gore [EMAIL PROTECTED] wrote:
Steve Martin wrote:
I am trying to create a PL/PGSQL function to return the values of the
fields in a record, e.g. 1 value per row in the output of the function.
How do you substitute a variable?
CREATE OR REPLACE
On 12:40 pm 07/23/08 Tom Lane [EMAIL PROTECTED] wrote:
That's fairly hard to believe. Care to offer some details?
I will dig that actual project and run explain analyze. Will likely not
have it till middle of next week though because of a monthly process
starting out Friday.
However, I do
On 12:00 pm 07/23/08 Shane Ambler [EMAIL PROTECTED] wrote:
INSERT INTO table (fld_y,fld_z) VALUES ('y','z')
I believe that is the most common way of doing it.
which is really doing:
INSERT INTO table (fld_x, fld_y,fld_z) VALUES (DEFAULT, 'y','z')
Correct.
So either one should be fine.
Please excuse my lack of mac knowledge. I installed postgresql 8.3 using the
mac os x 1 click installer onto my brand new powerbook. The install appeared
to go very smooth. If I go to Postgresql under Applications it appears as if I
can start and stop postgres and open pgadmin. I even
On Wed, Jul 23, 2008 at 1:58 PM, [EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:
Please excuse my lack of mac knowledge. I installed postgresql 8.3 using the
mac os x 1 click installer onto my brand new powerbook. The install appeared
to go very smooth. If I go to Postgresql under Applications
Date: Wed, 23 Jul 2008 14:12:45 -0400
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: [GENERAL] mac install question
CC: pgsql-general@postgresql.org
On Wed, Jul 23, 2008 at 1:58 PM, [EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:
Please excuse my lack of mac knowledge. I
On Wed, Jul 23, 2008 at 2:19 PM, [EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:
Well I got it from a link on postgresql.org. Of course it does say that it
is a beta installer.
http://www.postgresql.org/download/macosx
Well, hopefully the maintainer reads this mailing list then. :)
-Doug
--
On Wed, Jul 23, 2008 at 11:43 AM, Francisco Reyes
[EMAIL PROTECTED] wrote:
On 12:40 pm 07/23/08 Tom Lane [EMAIL PROTECTED] wrote:
That's fairly hard to believe. Care to offer some details?
I will dig that actual project and run explain analyze. Will likely not
have it till middle of next
He does, though he's currently on vacation, being forced to top-post
in the gmail mobile interface :-p
Can the OP send me (privately) the bitrock_installer log from /tmp please?
Thanks, Dave
On 7/23/08, Douglas McNaught [EMAIL PROTECTED] wrote:
On Wed, Jul 23, 2008 at 2:19 PM, [EMAIL PROTECTED]
Shane Ambler wrote:
Raymond O'Donnell wrote:
...
INSERT INTO table (fld_y, fld_z) VALUES ('y', 'z');
Another way is INSERT INTO table VALUES (NULL,'y','z')
of course you meant:
INSERT INTO table VALUES (DEFAULT,'y','z')
since Null would be wrongly insert NULL value instead
of using the
PostgreSQL 8.1.4
RHEL 4.x
So we have run into an interesting problem I want to know if anyone else has
encountered before. We have a scheduler process that 'hangs' on occasion and
we have isolated the issue to Postgres not returning any records when there are
actual records to return. Here
On 2:23 pm 07/23/08 Scott Marlowe [EMAIL PROTECTED] wrote:
However, I do have a current example where bitmap index scan was 3
times worse.
What is your work_mem set to?
For the examples that I posted it is
work_mem = 64MB
--
Sent via pgsql-general mailing list
On Jul 23, 2008, at 2:19 PM, [EMAIL PROTECTED] wrote:
Date: Wed, 23 Jul 2008 14:12:45 -0400
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: [GENERAL] mac install question
CC: pgsql-general@postgresql.org
On Wed, Jul 23, 2008 at 1:58 PM, [EMAIL PROTECTED]
[EMAIL PROTECTED]
Francisco Reyes [EMAIL PROTECTED] writes:
SET ENABLE_SEQSCAN TO OFF;
SET ENABLE_BITMAPSCAN TO OFF;
Aggregate (cost=25665216.10..25665216.11 rows=1 width=12) (actual
time=3088.894..3088.896 rows=1 loops=1)
- Nested Loop (cost=0.00..25662307.70 rows=387785 width=12)
(actual
Thanks for the log. It shows that the otool utility couldn't be found
(which should be a fatal error but that's another issue.
Is the otool program on your system anywhere? Afaik, it should be on
any Mac, but maybe it's part of xcode (i hope not- can anyone
confirm?)
In any case, re-running the
it's a command line utility.
On 7/23/08, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Frankly i have no idea what otool is?
Date: Wed, 23 Jul 2008 20:45:23 +0100
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: [GENERAL] mac install question
CC: pgsql-general@postgresql.org
that'll make it work, but doesn't fix the problem. :-(
On 7/23/08, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Well scott's suggestion seemed to work:
Ryan,
Try setting:
export DYLD_LIBRARY_PATH=/Library/PostgreSQL/8.3/lib:$DYLD_LIBRARY_PATH
and running the command
On 3:37 pm 07/23/08 Tom Lane [EMAIL PROTECTED] wrote:
Francisco Reyes [EMAIL PROTECTED] writes:
SET ENABLE_SEQSCAN TO OFF;
SET ENABLE_BITMAPSCAN TO OFF;
Aggregate (cost=25665216.10..25665216.11 rows=1 width=12) (actual
time=3088.894..3088.896 rows=1 loops=1)
- Nested Loop
Keaton Adams [EMAIL PROTECTED] writes:
Any ideas why the equality search wouldn't work in the first case, but after
reloading the table data it works just fine?
I think you're using floating-point timestamps and encountering a
roundoff issue.
regards, tom lane
--
Table layouts:
historical
Column | Type |Modifiers
---+--+--
record_id | integer | not null default
nextval('historical_record_id_seq'::regclass)
f3| integer
Francisco Reyes [EMAIL PROTECTED] writes:
On 3:37 pm 07/23/08 Tom Lane [EMAIL PROTECTED] wrote:
You might be more likely to get a sane plan if you had an index on
join_ids.customer_id.
There is an index in join_ids:
joinids_customerids_joinid btree (customer_id, joinid) WITH (fillfactor=98)
On 4:12 pm 07/23/08 Francisco Reyes [EMAIL PROTECTED] wrote:
Also, that plan is only 3 seconds.
Minor update.
A co-worker is using another DB.. and re-running my query after he did his
work.. now the query using the index scans takes 2 minutes instead of 3
seconds. 3 seconds was likely data
[EMAIL PROTECTED] writes:
Thanks for the log. It shows that the otool utility couldn't be found
(which should be a fatal error but that's another issue.
Is the otool program on your system anywhere? Afaik, it should be on
any Mac, but maybe it's part of xcode (i hope not- can anyone
On 23 Jul 2008, at 21:47, Tom Lane [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] writes:
Thanks for the log. It shows that the otool utility couldn't be found
(which should be a fatal error but that's another issue.
Is the otool program on your system anywhere? Afaik, it should be on
any Mac,
Hi, I can not figure out how to solve the problem with LOCALE. Example:
- 3 tables, each of them with a text or varchar column.
- The first table with text in German
- The second table with text in Czech
- the third one with text in English
How to force Postgre to use correct locale for ORDER
On Wed, Jul 23, 2008 at 11:49:38PM +0200, Michal Seidl wrote:
Hi, I can not figure out how to solve the problem with LOCALE. Example:
- 3 tables, each of them with a text or varchar column.
- The first table with text in German
- The second table with text in Czech
- the third one with
Michal Seidl escribió:
Hi, I can not figure out how to solve the problem with LOCALE. Example:
- 3 tables, each of them with a text or varchar column.
- The first table with text in German
- The second table with text in Czech
- the third one with text in English
You cannot do this in
This might seem like a silly question, but what are the implications of
PostgreSQL allowing developers to create custom catalogs?
For example, PostgreSQL currently uses the pg_catalog schema to store
system catalogs / relations / functions / etc. Has thought gone into
extending the scope to
Guys,
I've got a table with an indexed column of a numeric user
type (implemented in C). When I run a select on that table
in a form of :
select * from sometable where column = 89464;
sequential scan is used...
When I rewrite the query to use cast like this :
select * from sometable where
I installed Postgresql from RedHat 5EL RPMS from the site. When I try to start
it as a service, it fails and pgstartup.log contains one record per startup
attempt stating: runuser: warning: cannot change directory to /dev/null: Not a
directory.
Thanks!
Dave Page wrote:
The linker hardcodes library paths into exes and libs. We examine these
paths at install time using otool and rewrite them from the staging
paths on the build machine to whatever directory the user chose to
install to using install_name_tool(1).
Yep, the mac linker seems to
Bill Wordsworth wrote:
Thanks Chris and Karsten. I still don't quite understand why invalid
state/record-mismatch would also not trigger auto rollback.
If you should have put id 5 instead of id 2 as a foreign key, how is the
database going to know the difference? Both are valid id's and valid
[EMAIL PROTECTED] wrote:
Is the otool program on your system anywhere? Afaik, it should be on
any Mac, but maybe it's part of xcode (i hope not- can anyone
confirm?)
I have a few installs here.
otool is included inside /Developer/usr/bin
my working 10.4 that the dev tools is installed on
This is one of the many SQL bad habits you've likely picked up from
using MySQL. I'd highly suggest reading the pgsql users manual cover
to cover, you'll pick up a lot of good info on how to drive
postgresql. Other things that work in mysql but fail in pgsql include
inserting things that are
On Wed, Jul 23, 2008 at 10:22 PM, Artacus [EMAIL PROTECTED] wrote:
This is one of the many SQL bad habits you've likely picked up from
using MySQL. I'd highly suggest reading the pgsql users manual cover
to cover, you'll pick up a lot of good info on how to drive
postgresql. Other things
Dave Page [EMAIL PROTECTED] writes:
What are you using it for that you need it to be present at install
time?
The linker hardcodes library paths into exes and libs. We examine
these paths at install time using otool and rewrite them from the
staging paths on the build machine to whatever
Kevin Neufeld [EMAIL PROTECTED] writes:
This might seem like a silly question, but what are the implications of
PostgreSQL allowing developers to create custom catalogs?
For example, PostgreSQL currently uses the pg_catalog schema to store
system catalogs / relations / functions / etc. Has
59 matches
Mail list logo
