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 somethin
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 SET
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 tra
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 u
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 ca
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.
1
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','
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
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 t
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 wha
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 d
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 wit
"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.
T
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')
... whic
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
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 h
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
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 creat
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 Applica
> 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 knowl
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
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 PROTECTE
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 s
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 i
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 (pgsql-general@p
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 PROTE
"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)
>
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 i
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@postgre
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 r
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)
> > ->
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 (fi
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
> conf
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 BY
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 a
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 colu
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 also
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 out
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 t
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
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.
Alex Vinogradovs <[EMAIL PROTECTED]> writes:
> Is it possible to make it work properly without
> use of explicit casts ? Thanks!
Read
http://www.postgresql.org/docs/8.3/static/xindex.html
particularly the discussion of cross-type index operators.
regards, tom lane
--
Sen
> "Harvey, Allan AC" <[EMAIL PROTECTED]> writes:
> >> "Harvey, Allan AC" <[EMAIL PROTECTED]> writes:
> >>> creating template1 database in
> /upgrades/postgres/postgresql-8.3.3/src/test/regress/./tmp_che
> ck/data/base/1 ... =: is not an identifier
>
> > A diff of postgres.bki on the SCO system to
60 matches
Mail list logo
