On 12/10/2009 01:00 PM, dipti shah wrote:
> Hi,
>
> We have started using PostGreSQL for more than 3 months and it looks
> awesome. Currently, we have been suing superuser by default as login
> account. Now, the users are getting increased and we want to go away
> with using superuser by default.
dipti shah wrote:
Hi,
We have started using PostGreSQL for more than 3 months and it looks
awesome. Currently, we have been suing superuser by default as login
account. Now, the users are getting increased and we want to go away
with using superuser by default. We want to create the separate
On 10/12/2009 3:30 PM, dipti shah wrote:
Hi,
We have started using PostGreSQL for more than 3 months and it looks
awesome. Currently, we have been suing superuser by default as login
account. Now, the users are getting increased and we want to go away
with using superuser by default. We want to c
On 10/12/2009 4:21 PM, John R Pierce wrote:
dipti shah wrote:
Hi,
We have started using PostGreSQL for more than 3 months and it looks
awesome. Currently, we have been suing superuser by default as login
account. Now, the users are getting increased and we want to go away
with using superuser b
What a awesome response!!! Thanks a ton all of you.
Special Thanks to Craig for absolutely brillient reply. I will test all you
said and will get back if I have any questions.
Thanks,
Dipti
On Thu, Dec 10, 2009 at 2:07 PM, Craig Ringer
wrote:
> On 10/12/2009 4:21 PM, John R Pierce wrote:
>
>> d
Tom Lane writes:
> It's not impossible that we'll have to tweak pg_dump a bit; it's
> never had to deal with languages that shouldn't be dumped ...
Ah, the best would be to have extensions maybe. Then you could do this
in initdb, filling in template0:
CREATE EXTENSION plpgsql ...;
Then at crea
Hi,
a lot of attributes get stored as numbers.
Usually I built a small table with a numerical key-column and a
text-column to use in joins.
Is there a way to store them all in a new table that has a 3rd column
for group-numbers instead of having a single table for every
textattribute-group?
In response to Andreas :
> Maybe I'm all very wrong with my approach to the text-option store.
> This must be a common issue, though.
> Do you know a better way to do it?
No, i'm sorry.
AFAIK you are from germany, right?
We have a similar question in the german pg-forum:
http://www.pg-forum.de/s
I tried making the "ident" column a text instead of text[] in the beginning,
but searches were approximately of the same speed; so I voted for the array,
because this way there isn't even a possibility for the separator ("##") to
cause problems.
Anyway, the "ident BETWEEN ARRAY['foo', 'bar'] AND A
Hi
As Pg doesn't presently support client push for notifications arising
from LISTEN/NOTIFY, I'm wondering if anybody here has done any research
into the cheapest statement to issue to check for such notifications.
First: is it worth caring? Or is a `SELECT 1;' every few seconds from a
small
Craig Ringer writes:
> As Pg doesn't presently support client push for notifications arising
> from LISTEN/NOTIFY, I'm wondering if anybody here has done any research
> into the cheapest statement to issue to check for such notifications.
The backend certainly will push the notification. Maybe
The command's nothing out-of-the-ordinary:
#!/bin/bash
export LD_LIBRARY_PATH=/usr/local/pgsql/lib
#
# Set Variables
#
DAY_NUM=`/bin/date +"%d"`
MON_NUM=`/bin
Hello,
I'm using postgresql_autodoc tool (v. 1.40) to document Postgresql 8.4
databases and have a couple of questions. This tool works great in
documenting all of the database objects with the exception of triggers
and permissions. I can see in the Perl script where permissions
information a
On Wed, Dec 9, 2009 at 12:57 PM, Greg Smith wrote:
> You should turn on log_checkpoint in the postgresql.conf and confirm the
> slowdowns are happening around the same time as the checkpoint report gets
> written to the log files.
I have turned on log_checkpoints, and re-run the command. Check
On Thu, Dec 10, 2009 at 4:24 AM, dipti shah wrote:
> Special Thanks to Craig for absolutely brillient reply. I will test all you
> said and will get back if I have any questions.
agreed! you should add this commentary to the interactive document
page mentioned above thread. the manual sure coul
On Thu, Dec 10, 2009 at 8:23 AM, Craig Ringer
wrote:
> Hi
>
> As Pg doesn't presently support client push for notifications arising from
> LISTEN/NOTIFY, I'm wondering if anybody here has done any research into the
> cheapest statement to issue to check for such notifications.
notifications are s
Tom Lane wrote:
> Craig Ringer writes:
>> As Pg doesn't presently support client push for notifications arising
>> from LISTEN/NOTIFY, I'm wondering if anybody here has done any research
>> into the cheapest statement to issue to check for such notifications.
>
> The backend certainly will push
On Thursday 10 December 2009 7:27:54 am CG wrote:
> The command's nothing out-of-the-ordinary:
>
> #!/bin/bash
>
> export LD_LIBRARY_PATH=/usr/local/pgsql/lib
>
> #
> # Set Variables
> ##
Merlin Moncure wrote:
> On Thu, Dec 10, 2009 at 8:23 AM, Craig Ringer
> wrote:
>> Hi
>>
>> As Pg doesn't presently support client push for notifications arising from
>> LISTEN/NOTIFY, I'm wondering if anybody here has done any research into the
>> cheapest statement to issue to check for such noti
I didn't understand your meaning.
Regards,
Dipti
On Thu, Dec 10, 2009 at 8:44 PM, Vick Khera wrote:
> On Thu, Dec 10, 2009 at 4:24 AM, dipti shah
> wrote:
> > Special Thanks to Craig for absolutely brillient reply. I will test all
> you
> > said and will get back if I have any questions.
>
> a
On Wednesday 09 December 2009 11:34:39 pm Postgre Novice wrote:
> Can someone please share some light on this
>
>
>
> - Forwarded Message
> From: Postgre Novice
> To: pgsql-general@postgresql.org
> Sent: Wed, December 9, 2009 5:23:18 PM
> Subject: [GENERAL] ERROR: could not open rel
Craig Ringer writes:
> Tom Lane wrote:
>> The backend certainly will push the notification. Maybe you just have
>> a client-side-software issue?
> Testing with two plain old psql sessions to an 8.4.1 DB on linux (Ubuntu
> 9.10) here, I don't see the asynchronous notification until I send some
>
From: Adrian Klaver
To: pgsql-general@postgresql.org
Cc: Postgre Novice
Sent: Thu, December 10, 2009 10:23:21 PM
Subject: Re: Fw: [GENERAL] ERROR: could not open relation with OID 59132
On Wednesday 09 December 2009 11:34:39 pm Postgre Novice wrote:
> Can s
On Thursday 10 December 2009 9:17:47 am Postgre Novice wrote:
>
> At a guess I am thinking it has to do with this:
>
> "All constraints on all partitions of the master table are examined during
> constraint exclusion, so large numbers of partitions are likely to increase
> query planning time cons
On Wed, Dec 9, 2009 at 4:53 AM, Postgre Novice wrote:
> Hello ,
>
> after google search i havent found any solution or clue for this specific
> case:
>
> Background:
> Postgresql: 8.3.0
You're missing over a year of updates, and there may well be a bug in
that version that's since been fixed. Do
Thanks for the suggestion. I'm not sure what you mean when you say I should
restore to a file. Do you mean I should dump the database to an SQL file
instead of the "compressed" format?
What do you think I will find?
In the database dump, it is including a row that should be marked as deleted.
CG wrote:
Thanks for the suggestion. I'm not sure what you mean when you say I should restore to a
file. Do you mean I should dump the database to an SQL file instead of the
"compressed" format?
he meant...
pg_restore -f outputfile.sql yourdumpfile
this will convert the dumpfile to SQ
- "CG" wrote:
> Thanks for the suggestion. I'm not sure what you mean when you say I
> should restore to a file. Do you mean I should dump the database to an
> SQL file instead of the "compressed" format?
See Johns answer.
>
> What do you think I will find?
>
> In the database dump, it
# select version():
PostgreSQL 8.1.10 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
(Gentoo 4.1.2 p1.0.1)
(I know, I know, it's an old version of PG whose sources aren't even available
today. Nonetheless, we've had great success with it.)
# uname -a
Linux servername 2.6.24.3-TCDI #16 S
Hello,
I recently attempted an upgrade on one of our servers from 8.2.6 to
8.4.1. I created two separate dumps for the database, separating the
schema and data. I did separate dumps because I need to expand the
size of a column in one of the tables.
After the schema restore, everything was goin
I need to apply arbitrary masking to a string (think displaying a phone
number).
I searched around but didnt find anything and wondered if there was such
a thing.
I had some perl code that already did it, so it was easy to make it into
a stored proc. I'll paste it below.
use it like:
sele
When PostgreSQL calls a C function I get all kinds of interesting
information in the
struct FunctionCallInfoData
and
struct FmgrInfo
(details at bottom). I was hoping to get the oid of the expected
return type somewhere, but I don't see it. Am I missing something?
I'm trying to av
"J. Greg Davidson" writes:
> When PostgreSQL calls a C function I get all kinds of interesting
> information in the
> struct FunctionCallInfoData
> and
> struct FmgrInfo
> (details at bottom). I was hoping to get the oid of the expected
> return type somewhere, but I don't see it. Am
> Right?
Right. The way I do it, very roughly:
Pqconnectdb(...)
PQexec( mDbConn, "listen notify" );
int dbsock = PQsocket( mDbConn );
mKq = kqueue();
struct kevent kev[1], kevs[1];
EV_SET( &kev[0], dbsock, EVFILT_READ, EV_ADD | EV_ENABLE, 0, 0, 0 );
kevent( mKq, kev, 1, NULL, 0, NULL );
"Eric B. Ridge" writes:
> Postgres locked up. All existing backends (roughly 100) couldn't execute
> commands. They'd just hang. One random backend I selected had this
> backtrace:
> (gdb) bt
> #0 0xb7f7f410 in __kernel_vsyscall ()
> #1 0xb7e37a6b in semop () from /lib/libc.so.6
> #2 0x08
On Dec 10, 2009, at 6:28 PM, Tom Lane wrote:
> It looks like somehow the SInvalLock got stuck --- that would account
> for both the stack traces you show.
What's a SInvalLock? I looked at the code/comments for
ReceiveSharedInvalidMessages(), but it didn't make much sense out of context.
> I'
Glen Barber writes:
> When schema/data are separated, is the restore treated as INSERTs?
No, but you'll still get killed on performance by other factors,
particularly incremental index building and retail foreign key checks.
I recommend a close read of
http://www.postgresql.org/docs/8.4/static/po
"Eric B. Ridge" writes:
> On Dec 10, 2009, at 6:28 PM, Tom Lane wrote:
>> It looks like somehow the SInvalLock got stuck --- that would account
>> for both the stack traces you show.
> What's a SInvalLock? I looked at the code/comments for
> ReceiveSharedInvalidMessages(), but it didn't make
Hi
Are there any plans to embed crosstab within the main release of
Postgres, rather than as a Contrib component.
Also, are there plans to enhance crosstab along the lines of Oracle 11g
of pivot command?
All the best Simon
--
Simon Windsor
Eml: simon.wind...@cornfield.me.uk
Tel: 01454 617689
M
Simon Windsor wrote:
Are there any plans to embed crosstab within the main release of
Postgres, rather than as a Contrib component.
contrib components are within the main release of PostgreSQL as far as
the core project is concerned. Sometimes downstream packagers fail to
include them or bre
Dimitri Fontaine wrote:
> Tom Lane writes:
> > It's not impossible that we'll have to tweak pg_dump a bit; it's
> > never had to deal with languages that shouldn't be dumped ...
>
> Ah, the best would be to have extensions maybe. Then you could do this
> in initdb, filling in template0:
> CREAT
Stephen Tyler wrote:
So a "typical" checkpoint is around 200K buffers (1.5GBytes, 40% of
max), taking around 150 seconds to write (10MByte/second, 1300
buffers/second), and around 150 seconds to sync.
Your problem may very well be plain old lack of disk I/O throughput, and
fsync forcing some ca
Simon Windsor wrote:
> Are there any plans to embed crosstab within the main release of
> Postgres, rather than as a Contrib component.
>
> Also, are there plans to enhance crosstab along the lines of Oracle 11g
> of pivot command?
I think the standard spelling of this feature is GROUPING SETS a
Stephen Tyler wrote:
I don't understand how maxwritten_clean could be as high as 95058, and
increment at more than 1 per second. This is a process count, not a
buffer count? How often is the background cleaner launched? Does
that mean I need to massively increase bgwriter_lru_maxpages, and
Greg Smith wrote:
Stephen Tyler wrote:
So a "typical" checkpoint is around 200K buffers (1.5GBytes, 40% of
max), taking around 150 seconds to write (10MByte/second, 1300
buffers/second), and around 150 seconds to sync.
Your problem may very well be plain old lack of disk I/O throughput,
and fs
I've been having my students post journal entries to a Drupal site, and we just
noticed that some of them have been copying from each other (surprise,
surprise). If this was done with reports or other submissions, we'd just
submit the deliverables to TurnItIn.com and go from there.
In this cas
Bill Todd wrote:
What are the two best platforms for PostgreSQL in your opinion?
There are three good platforms, each with a different set of strengths
and weaknesses:
-Linux: Lots of driver support even for very recent things available.
Really broad universe of available add-on packages.
On Thu, Dec 10, 2009 at 9:21 PM, Stephen Tyler wrote:
> I have turned on log_checkpoints, and re-run the command. Checkpoints are
> being written every 220 to 360 seconds. About 80% are "time" and 20%
> "xlog". Here are a representative sample:
>
> 10/12/09 5:12:59 PMorg.postgresql.postgres
I've been having my students post journal entries to a Drupal site, and we just
noticed that some of them have been copying from each other (surprise,
surprise). If this was done with reports or other submissions, we'd just
submit the deliverables to TurnItIn.com and go from there.
In this cas
2009/12/11 Alvaro Herrera :
> Simon Windsor wrote:
>
>> Are there any plans to embed crosstab within the main release of
>> Postgres, rather than as a Contrib component.
>>
>> Also, are there plans to enhance crosstab along the lines of Oracle 11g
>> of pivot command?
>
> I think the standard spell
Hi All.
I have a program which import some 6000-1 records in Postgres
database. However the import of data is successful but the postgres.exe
almost takes up entire memory and CPU.
I could see in the process list there are lot of postgres.exe are
running and one of the postgres.exe has
a.bhattacha...@sungard.com wrote:
Hi All.
I have a program which import some 6000-1 records in Postgres
database. However the import of data is successful but the
postgres.exe almost takes up entire memory and CPU.
I could see in the process list there are lot of postgres.exe are
In response to a.bhattacha...@sungard.com :
> Hi All.
>
>
>
> I have a program which import some 6000-1 records in Postgres database.
How? With COPY or with INSERT? Within one single transaction or every
Insert in an own transaction?
> I could see in the process list there are lot of pos
53 matches
Mail list logo
