Re: [GENERAL] "1-Click" installer problems

[John R Pierce]

John Gage wrote:

The 8.4.2 documentation says:

"The default user name is your Unix user name, as is the default 
database name."


when you as a user connect to the database server the commands like 
psql, pg_dump, etc all use your unix username as the default for the 
database username, and your username as teh default for the database 
name, unless you specify a different user and/or database on hte command 
line.




--
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] "1-Click" installer problems

[John Gage]

The 8.4.2 documentation says:

"The default user name is your Unix user name, as is the default  
database name."


Not so much.  My one-click installer creates a user 'postgres' who  
becomes the default user name...as well as the owner of the data file.


Is postgres arguing with itself here? Or at least in its one-click  
incarnation on the Mac.


In Nikhil's world, I would say that the clients are pretty carefully  
tightened down in terms of privileges.  And apparently Vista has  
enabled more tightening down.


John



On Apr 2, 2010, at 6:43 AM, Craig Ringer wrote:

I log in as an Administrator-enabled user, but have UAC turned on.  
This means that in fact I'm using non-admin rights unless/until I  
accept a UAC prompt.



--
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] "1-Click" installer problems

[John Gage]

I will bet a bucket of day-old squid that this is a user rights problem.

On Apr 2, 2010, at 6:43 AM, Craig Ringer wrote:

I log in as an Administrator-enabled user, but have UAC turned on.  
This means that in fact I'm using non-admin rights unless/until I  
accept a UAC prompt.



--
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] "1-Click" installer problems

[Craig Ringer]

On 1/04/2010 5:35 PM, Nikhil G. Daddikar wrote:


It is not the count that matters. It is the quality. Even after giving
logs, your folks are unable to figure out the problem. I am facing the
same problem that was faced by a lot of users on the forum and not one
was resolved successfully. Otherwise I would've moved ahead on my own. I
am interested in fixing the installer. I am not interested in manual
steps to get it working on my PC because there are a hundred other PCs
that I have to worry about and a fix in the installer would be right
step ahead. But someone has to admit that there is a problem in the
installer. All arguments in this thread are junk because 8.3 installer
for win32 from postgresql.org WORKS using the same conditions on
Vista/2003/7 everywhere. It is nothing to do with APPDATA or any such
thing.




Something that worked was broken, it's that simple. And nobody
wants to know what was.


Actually, we all want to know what it was. But you're not helping us 
find out - see Sachin's request that you run the initcluster.vbs script 
to collect some debug info.


One thing you need to understand is that nothing has been "broken". 
Here's how it was:




  MSI INSTALLERONECLICK INSTALLER
8.3[available]   [available]
8.4   [discontinued] [available]


The MSI installer is *completely* *different*. It's not like Pg switched 
over. The .msi installer was dropped because it was silly to duplicate 
work that EDB was going to do anyway.


You perceive something as having been broken, but in fact you're using a 
_different_ _product_ now. It's not working for you, and that is 
something that needs addressing, but it's not like there was some change 
that can just be reverted.


Now, clearly there's something _different_ about your machines that 
makes the EDB installer fail on them, and not most systems. Let's try to 
figure out what it is.




The reason I posted this in the general newsgroup is because I thought
others would like to know what's going on. But I think this is a
newsgroup with a bunch of inflated egos who want to do everything else
rather than address the problem.


You're rather selectively ignoring the people who're asking you for more 
details, and trying to explain that since it works for them, they need 
to figure out what makes your setup different in order to fix the problem.


Nobody's claiming the EDB installer is perfect anyway. They're just 
pointing out that "doesn't work for you" isn't the same as "doesn't work 
for anyone, broken and awful".


--
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

Re: [GENERAL] "1-Click" installer problems

[Craig Ringer]

On 1/04/2010 3:19 PM, Nikhil G. Daddikar wrote:




On 01-04-2010 12:39, Thomas Kellerer wrote:

Nikhil G. Daddikar, 01.04.2010 08:04:


In about 30 seconds I found the following unanswered threads relating to
installation on Windows Vista. If anybody is interested I can find more.


The problem with this kind of statistics is that you will only find
people who complain, you'll never find people who do not complain
because they have no problems. Actually that's true for all internet
forums or mailing lists: you'll seldomly find people posting something
like "Hey everything works fine, I had no problems".


I agree... but they are still unanswered. And what else can I do. I am
facing problems on multiple computers, my customers are facing problems
as well. NOBODY till date has managed to install 8.4 on Vista using the
1-click installer and EVERYBODY has managed to install the 8.3 installer
(from postgresql.org) on Vista. DOES THIS COUNT AS A VALID STATISTIC?
And yes, i have tried installing it in C:\Postgresql as well.
Interesting to note that 8.3 installer from postgresql.org installs
perfectly in 'C:\Program Files' even on Vista. No use blaming Windows
all the time. It is the installer that is buggy.


Yet I can install 8.4 from the one-click on my Vista box with no issues.

So - what makes your systems different? Let's figure this out and fix 
it. I'm going to provide a profile of my machine's configuration. Please 
compare it with yours, focusing on common points across the machines 
you've seen issues with.


OTHER PEOPLE, PLEASE CHIME IN WITH SIMILAR DETAILS. (Perhaps we need a 
trouble-shooting data collector app. Hmm. I might be up to that...)


My Vista machine runs build 6002 (Service Pack 2) according to winver.exe .

I log in as an Administrator-enabled user, but have UAC turned on. This 
means that in fact I'm using non-admin rights unless/until I accept a 
UAC prompt.


For a virus scanner, it has Microsoft Security Essentials installed, but 
real-time protection is turned off. There is no resident anti-spyware 
software. It does not have 3rd-party desktop search installed.


It's never had 8.3 installed, by msi or oneclick. (When you provide 
this, make sure to mention if/how you uninstalled 8.3 before installing 
8.4, whether you removed the postgres user account, etc).


A running process list from Process Explorer is attached.

When I launch winver.exe there are no non-Microsoft DLLs loaded 
according to Process Explorer. (View, Show lower pane, DLLs). So there's 
nothing hooking into every process launched on the machine.



Please follow up with equivalent details on your systems. Meanwhile I'm 
going to uninstall 8.4 and reinstall it. If that works I'll delete the 
user account and all registry entries, install 8.3 .msi, uninstall that, 
and then install 8.4.  See what might be going on.


--
Craig Ringer
Process PID CPU Private Bytes   Working Set Description Company 
Name
System Idle Process 0   95.71   0 K 24 K
 Interrupts n/a 0 K 0 K Hardware Interrupts 
 DPCs   n/a 0 K 0 K Deferred Procedure Calls
 System 4   0 K 2,724 K 
  smss.exe  408 292 K   100 K   
csrss.exe   544 2,100 K 1,920 K 
wininit.exe 600 1,228 K 128 K   
 services.exe   644 2,532 K 3,016 K 
  svchost.exe   840 3,536 K 3,648 K Host Process for Windows 
Services   Microsoft Corporation
   mobsync.exe  12923,088 K 5,928 K Microsoft Sync Center   
Microsoft Corporation
  nvvsvc.exe884 2,396 K 368 K   NVIDIA Driver Helper Service, 
Version 197.13NVIDIA Corporation
   nvvsvc.exe   15684,688 K 2,444 K 
  svchost.exe   916 3,264 K 3,332 K Host Process for Windows 
Services   Microsoft Corporation
  MsMpEng.exe   956 132,424 K   56,292 KAntiMalware 
Service Executable  Microsoft Corporation
  svchost.exe   106415,876 K7,092 K Host Process for 
Windows Services   Microsoft Corporation
   audiodg.exe  130812,596 K7,760 K 
  svchost.exe   109665,324 K62,264 KHost Process 
for Windows Services   Microsoft Corporation
   dwm.exe  26722,964 K 536 K   Desktop Window Manager  
Microsoft Corporation
  svchost.exe   113251,808 K50,696 KHost Process 
for Windows Services   Microsoft Corporation
   taskeng.exe  552 2,140 K 2,236 K 
   taskeng.exe  27729,524 K 3,384 K Task Scheduler Engine   
Microsoft Corporation
  svchost.exe   13443,244 K 2,532 K Host Process for Windows 
Services   Microsoft Corporation
  SLsvc.exe 13966,812 K 1,392 K Microsoft Software Licensing 
ServiceMicrosoft Corporation
  svchost.exe   1488   

Re: [GENERAL] How to setup a column constraint for an integer type? Default 0 if not defined

[John R Pierce]

Wang, Mary Y wrote:

Hi,


I'm trying to port some source code to Postgres 8.3.8 and have been 
encountering some surprises :-(
I would like to set up a column constraint for an integer type, so that when 
the value is undefined, it would assign a default value of 0 to that column.
How would I do that in 8.3.8?  Please provide an example.
  


when you say, 'value is undefined' do you mean, if an insert is done 
without specifying that field ?


in that case, ou wouldn't use a constraint, you'd use a DEFAULT value, 
like...


   CREATE TABLE tblname (
  
 somefield INTEGER DEFAULT 0,
  
  );

or, if the table already exists...

   ALTER TABLE tblname ALTER COLUMN somefield SET DEFAULT 0;




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to setup a column constraint for an integer type? Default 0 if not defined

[Wang, Mary Y]

Hi,


I'm trying to port some source code to Postgres 8.3.8 and have been 
encountering some surprises :-(
I would like to set up a column constraint for an integer type, so that when 
the value is undefined, it would assign a default value of 0 to that column.
How would I do that in 8.3.8?  Please provide an example.

Mary






-- 
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] Accessing Windows install of PostgreSQL via cygwin

[Filip Rembiałkowski]

You can see that python is trying to connect to postgres via unix
socket.  In cygwin that's rather not possible.
Just switch the client to TCP mode.  There should be host/port in
connection settings.

2010/4/1, nerdydork :
> I have had a development environment set up in Windows for Django.
> Recently I moved all my python/django setup to cygwin because I got
> tired of the windows command line.
>
> Everything seems to be working except the PostgreSQL connection. I
> understand that I can install postgres inside of cygwin and access it
> there, but is it possible to access my windows install of postgres
> from within cygwin? If so, how? I get errors trying to connect to
> localhost:54
>
>
>   File "/usr/lib/python2.5/site-packages/django/db/backends/
> postgresql_psycopg2/base.py", line 98, in _cursor
> self.connection = Database.connect(**conn_params)
> psycopg2.OperationalError: could not connect to server: No such file
> or directory
> Is the server running locally and accepting
> connections on Unix domain socket "/tmp/.s.PGSQL.5432"?32
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
Wysłane z mojego urządzenia przenośnego

Filip Rembiałkowski
JID,mailto:filip.rembialkow...@gmail.com
http://filip.rembialkowski.net/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Accessing Windows install of PostgreSQL via cygwin

[nerdydork]

I have had a development environment set up in Windows for Django.
Recently I moved all my python/django setup to cygwin because I got
tired of the windows command line.

Everything seems to be working except the PostgreSQL connection. I
understand that I can install postgres inside of cygwin and access it
there, but is it possible to access my windows install of postgres
from within cygwin? If so, how? I get errors trying to connect to
localhost:54


  File "/usr/lib/python2.5/site-packages/django/db/backends/
postgresql_psycopg2/base.py", line 98, in _cursor
self.connection = Database.connect(**conn_params)
psycopg2.OperationalError: could not connect to server: No such file
or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?32

-- 
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] Wikipedia entry - AmigaOS port - error?

[Glyn Astill]

--- On Thu, 1/4/10, Tom Lane  wrote:

> > But I do remember there was a set of libs called
> ixemul (http://aminet.net/package/dev/gg/ixemul-bin) that a lot
> of people used to port unix apps to the Amiga with, probably
> not enough to port postgres though.
> 
> Ah, I wondered if there might not be such a thing. 
> However, according
> to http://en.wikipedia.org/wiki/Ixemul.library it doesn't
> have support
> for fork(), which would have been a complete showstopper
> back in the day
> (though since the Windows port in 8.0 we can live without
> it).  So that
> pretty much kills any thought that it might've once worked
> and we just
> forgot.
> 

And now you mention it, I remember that was exactly the reason why the last 
version of perl for Amiga was 5.7.2, it says there was support for vfork() but 
not fork()

http://theory.uwinnipeg.ca/CPAN/perl/pod/perlamiga.html#perl_5_8_0_broken_in_amigaos

Glyn




-- 
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] temporary table

[Amol Chiplunkar]

A crude way would be to put it in a block and ignore the exception

BEGIN
   -- create temp table
   EXCEPTION
   WHEN DUPLICATE_TABLE THEN
   -- Table already exists,
   NULL;
   END;

thx
- Amol

Szymon Guz wrote:
I want to create a temp table in a trigger... but this must be created 
max once for each transaction. I'd like to check in the trigger if the 
table exists. 
How can I check if a table is a temporary table?


regards
Szymon Guz



--
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] Wikipedia entry - AmigaOS port - error?

[Tom Lane]

Glyn Astill  writes:
> But I do remember there was a set of libs called ixemul 
> (http://aminet.net/package/dev/gg/ixemul-bin) that a lot of people used to 
> port unix apps to the Amiga with, probably not enough to port postgres though.

Ah, I wondered if there might not be such a thing.  However, according
to http://en.wikipedia.org/wiki/Ixemul.library it doesn't have support
for fork(), which would have been a complete showstopper back in the day
(though since the Windows port in 8.0 we can live without it).  So that
pretty much kills any thought that it might've once worked and we just
forgot.

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] Wikipedia entry - AmigaOS port - error?

[Glyn Astill]

--- On Thu, 1/4/10, Tom Lane  wrote:

> > Just noticed on the wikipedia page under rdbms, it
> lists postgresql as available on AmigaOS.
> 
> > http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems
> 
> > Is this just an error, as I see edb advanced server is
> also listed as available, or was there some working code at
> some point in time?
> 
> I think it's probably bogus.  There's some mention in
> very old versions
> of the ports list of being able to run on top of NetBSD on
> Amiga
> hardware.  But unless somebody had code to duplicate
> the Unix syscall set
> on AmigaOS, there'd have been no way to make PG run on
> that.
> 

Thanks Tom, I thought as much, I recall the Amiga community being full of 
vaporware.

But I do remember there was a set of libs called ixemul 
(http://aminet.net/package/dev/gg/ixemul-bin) that a lot of people used to port 
unix apps to the Amiga with, probably not enough to port postgres though.

Glyn








-- 
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] [Solved] 8.3 Stats Collector Stuck at 100% CPU

[Josh Kupershmidt]

On Thu, Apr 1, 2010 at 4:22 PM, Tom Lane  wrote:
> Do you have a copy of the 1.2GB file and would you be willing to send me
> it if so?  There shouldn't be any especially private info in there, just
> table OIDs and access counts.  (1.2GB would be a lot of data to mail but
> I bet it gzips down to a lot less.)

I didn't keep a copy last night, and I don't think we have a
filesystem-level backup for this machine :-(

I will try to remember to keep a copy if this happens again.
Josh

-- 
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] Array value syntax and escaping

[Peter Bex]

On Thu, Apr 01, 2010 at 04:37:23PM -0400, Tom Lane wrote:
> There is not, but you could always look at the source for array_out and
> array_in if you want definitive answers ;-).  Personally what I'd do is
> always double-quote each non-null array element; then the rules reduce
> to "backslash any backslashes or double quotes".

That's what I'm doing right now.

> If you're working in a sane client encoding (not SJIS for instance)
> this is pretty trivial.

That's a relief :)
OTOH, for a generic library, it's not a good idea to make such assumptions..

> The problem with that is that the necessarily-arbitrary API would
> probably add as much or more complexity as would be saved.  If C had
> a simple and universally-followed convention for variable-size arrays,
> it'd be easier to provide useful helpers ...

What I was proposing is a simple escaper for string values, nothing
more.  Putting array decoration around those is trivial, encoding the
strings is the tricky part.

However, this got me thinking: It could use the same system as
environment and argv values in C, or the new connection procedures of
libpq; a null-terminated list of string pointers.

But then the question is how to encode nested arrays.  I guess it's
feasible to pass the array depth as an extra argument to the escaping
procedure, since we know arrays cannot have variable depths between
elements.  This could really work and wouldn't need to be overly complex.

It wouldn't save a lot of complexity, but it would save some wheel
reinvention in a case where there's room for error, just like PQescape*.
You could argue that PQescape* don't save a lot of complexity either,
yet those are considered a good idea.  I don't see how this is any
different.

> > I briefly considered "abusing" the PQescapeIdentifier procedure for
> > escaping since the syntax for literals inside arrays seems to be exactly
> > like that of SQL identifiers, but I'm not 100% sure about that and I
> > also think the PQescapeIdentifier procedure shouldn't be overloaded for
> > this purpose.
> 
> Well, that wouldn't work anyway, since backslashes aren't special in
> identifiers.

Good thing I didn't abuse it, then :)

Cheers,
Peter
-- 
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
-- Donald Knuth

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] temporary table

[Szymon Guz]

I want to create a temp table in a trigger... but this must be created max
once for each transaction. I'd like to check in the trigger if the table
exists.
How can I check if a table is a temporary table?

regards
Szymon Guz

Re: [GENERAL] Array value syntax and escaping

[Tom Lane]

Peter Bex  writes:
> I am currently adding array value handling to the PostgreSQL interface
> for the Chicken Scheme compiler[*] and I was wondering if there's a more
> detailed documentation for the exact syntax of arrays than the short
> natural language explanation in the manual.

There is not, but you could always look at the source for array_out and
array_in if you want definitive answers ;-).  Personally what I'd do is
always double-quote each non-null array element; then the rules reduce
to "backslash any backslashes or double quotes".  If you're working in
a sane client encoding (not SJIS for instance) this is pretty trivial.

> IMHO, it would be a Good Thing to have helper procedures in libpq.

The problem with that is that the necessarily-arbitrary API would
probably add as much or more complexity as would be saved.  If C had
a simple and universally-followed convention for variable-size arrays,
it'd be easier to provide useful helpers ...

> I briefly considered "abusing" the PQescapeIdentifier procedure for
> escaping since the syntax for literals inside arrays seems to be exactly
> like that of SQL identifiers, but I'm not 100% sure about that and I
> also think the PQescapeIdentifier procedure shouldn't be overloaded for
> this purpose.

Well, that wouldn't work anyway, since backslashes aren't special in
identifiers.

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] transaction control in pl/pgsql

[Merlin Moncure]

On Thu, Apr 1, 2010 at 6:22 AM, Birgit Laggner
 wrote:
> Dear list,
>
> I have some data (big size) and I've written a long function in pl/pgsql
> which processes the data in several steps. At a test run my function
> aborted because of memory exhaustion. My guess is, that everything what
> happens during the function transaction is stored in the memory - until
> it's full... So, my idea for solving that problem would be to cut the
> big function into smaller functions. But, I don't want to write 30
> function calls at the end - I would rather like to have one function
> which is calling all these small functions, so I would only have to
> write one sql-query at the end. What I fear is either, that, if this
> function calls the other functions, everything is only one trancaction
> again and I get memory overflow once more.

I don't know all the specifics of your case but ultimately there are
limits to what you can reasonably do in a single transaction,
especially if you are writing to the database.  If you push the limit
the database starts to push back.  Transactions generally should be as
short as possible.  Long transactions inhibit the ability of the
database to do certain types of maintenance on itself and have other
issues like bad performance and memory exhaustion.

Regardless, of how many separate functions/savepoints/begin/end blocks
your 'outer' function calls, your entire set of work is going to
operate within the context of a single transaction.  This is an iron
clad rule which (at present) there is no work around for.  For this
reason certain classes of data processing must unhappily be done on
the client side, introducing another language and forcing all the data
back and forth through the protocol.

In the future, it may be possible to execute pl/pgsql-ish type of code
in the backend that allows explicit transaction control. This feature
might be a 'stored procedure', or there might be some other type of
nomenclature to distinguish functions that manage their own
transaction state.

merlin

-- 
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] [Solved] 8.3 Stats Collector Stuck at 100% CPU

[Tom Lane]

Josh Kupershmidt  writes:
>> Hm.  It sounds like you are "leaking" stats collector table entries for
>> some reason.  It would be good to fix the underlying problem rather than
>> just resign yourself to a manual workaround.  Is there anything unusual
>> about your workload that might trigger this?

> Don't think the database setup is that unusual. I did overestimate how
> quickly pgstat.stat is growing; it's only gone up to 800KB in the 20
> hours since I ran pg_stat_reset(). I thought it was growing 4MB per
> day because last night it had grown to 500KB in just 3 hours. Also, it
> had ballooned to 1.2 GB after running for around a year, I think.

What would be expected is for it to ramp up fairly quickly to one entry
per table in the database, and then stabilize.  Maybe the 1.2GB figure
represents the fallout from some strange event rather than a gradual
leakage.

> Relevant stats-related info I can think of:
>  * default_statistics_target = 10
>  * I don't think any tables have had ALTER TABLE SET STATISTICS done
>  * the two really active databases have 2300 and 490 rows in pg_class
>  * mostly bulk updates/inserts
>  * PGDATA is 1.6 TB

> If there's some useful debugging info on the stats collector process I
> can gather from the server, I'd be happy to try.

Do you have a copy of the 1.2GB file and would you be willing to send me
it if so?  There shouldn't be any especially private info in there, just
table OIDs and access counts.  (1.2GB would be a lot of data to mail but
I bet it gzips down to a lot less.)

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] [Solved] 8.3 Stats Collector Stuck at 100% CPU

[Josh Kupershmidt]

> Hm.  It sounds like you are "leaking" stats collector table entries for
> some reason.  It would be good to fix the underlying problem rather than
> just resign yourself to a manual workaround.  Is there anything unusual
> about your workload that might trigger this?

Don't think the database setup is that unusual. I did overestimate how
quickly pgstat.stat is growing; it's only gone up to 800KB in the 20
hours since I ran pg_stat_reset(). I thought it was growing 4MB per
day because last night it had grown to 500KB in just 3 hours. Also, it
had ballooned to 1.2 GB after running for around a year, I think.

Relevant stats-related info I can think of:
 * default_statistics_target = 10
 * I don't think any tables have had ALTER TABLE SET STATISTICS done
 * the two really active databases have 2300 and 490 rows in pg_class
 * mostly bulk updates/inserts
 * PGDATA is 1.6 TB

If there's some useful debugging info on the stats collector process I
can gather from the server, I'd be happy to try.

Josh

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Array value syntax and escaping

[Peter Bex]

Hello all,

I am currently adding array value handling to the PostgreSQL interface
for the Chicken Scheme compiler[*] and I was wondering if there's a more
detailed documentation for the exact syntax of arrays than the short
natural language explanation in the manual.

There doesn't appear to be a helper function in libpq to escape (and
unescape) string values for use inside array values and I'm concerned
that my homebrew procedures might not be foolproof.

If I understand correctly, the connection's character encoding is also
used to determine how strings sent by "PQsendQueryParams parameters"
(I don't know if there's an unambiguous name for those) are parsed, and
it is conceivable that either through bogus or malicous input a
multibyte array string could be wrongly escaped, which means one array
value could "break out" of its proper position in the array, resulting
in an array of different length or contents than intended.

IMHO, it would be a Good Thing to have helper procedures in libpq.
That would fix the problem once and for all in one place.

I briefly considered "abusing" the PQescapeIdentifier procedure for
escaping since the syntax for literals inside arrays seems to be exactly
like that of SQL identifiers, but I'm not 100% sure about that and I
also think the PQescapeIdentifier procedure shouldn't be overloaded for
this purpose.

Cheers,
Peter

[*] http://chicken.wiki.br/eggref/4/postgresql
-- 
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
-- Donald Knuth

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Lifekeeper

[Brad Nicholson]

Hi,

Is anyone using Lifekeeper for Linux availability with Postgres?

If so, what are your thoughts on it?  Work as advertised?  Any dangerous
gotchas?

-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.



-- 
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] PostgreSQL on Windows

[Mark Vantzelfde]

Could it be that pg_ctl uses some sort of unix cmd like ps to try to
determine with the postgres processes are running? Using ps from a windows
command prompt does not show the postgres processes started as services.

Mark

On Mon, Mar 29, 2010 at 1:54 PM, Mark Vantzelfde wrote:

> Same result.
>
>
> On Mon, Mar 29, 2010 at 1:51 PM, Raymond O'Donnell  wrote:
>
>> On 29/03/2010 18:38, Mark Vantzelfde wrote:
>> > I am running 8.4 on a Windows Vista system. The software was installed
>> using
>> > the auto-install process. PostgreSQL starts successfully as a service
>> when
>> > the system boots. The running postgres* processes can be verified via
>> Task
>> > Manager. I can run psql from the command prompt. I have the PGDATA env
>> > variable set to the folder where the PostgreSQL data sits. Running the
>> > pg_ctl status command from the command prompt returns "pg_ctl: no server
>> > running". Ideas why pg_ctl doesn't know about the running services?
>>
>> Have you tried running it with the -D option instead of the env
>> variable? No idea if it will make any difference...just a wild guess.
>>
>> Ray.
>>
>> --
>> Raymond O'Donnell :: Galway :: Ireland
>> r...@iol.ie
>>
>
>
>
> --
> Mark Vantzelfde
> NetMasters, Inc.
>



-- 
Mark Vantzelfde
NetMasters, Inc.

Re: [GENERAL] [HACKERS] Postgres 9.1 - Release Theme

[Adrian Klaver]

On 04/01/2010 09:54 AM, Scott Marlowe wrote:

On Thu, Apr 1, 2010 at 10:05 AM, David E. Wheeler  wrote:

On Apr 1, 2010, at 3:01 AM, Magnus Hagander wrote:


I prefer to dump all my data in a big text file and grep it for the information 
I need.


As long as you implement your own grep, that sounds about on par with
the current trends! Go for it!


Well, first you have to implement your own compiler. Also a lexer and a parser.


All that will be for naught unless you hand wire your own logic
boards.  I mean really, come on.



Actually I think this calls for quantum computing 
(http://en.wikipedia.org/wiki/Quantum_computer). The intersection of 
NoSQL and Quantum decoherence is almost to good to be true.


--
Adrian Klaver
adrian.kla...@gmail.com

--
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] [HACKERS] Postgres 9.1 - Release Theme

[Joshua D. Drake]

On Thu, 2010-04-01 at 10:54 -0600, Scott Marlowe wrote:
> On Thu, Apr 1, 2010 at 10:05 AM, David E. Wheeler  
> wrote:
> > On Apr 1, 2010, at 3:01 AM, Magnus Hagander wrote:
> >
> >>> I prefer to dump all my data in a big text file and grep it for the 
> >>> information I need.
> >>
> >> As long as you implement your own grep, that sounds about on par with
> >> the current trends! Go for it!
> >
> > Well, first you have to implement your own compiler. Also a lexer and a 
> > parser.
> 
> All that will be for naught unless you hand wire your own logic
> boards.  I mean really, come on.

I hate April 1st.

> 


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or 
Sir.


-- 
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] Wikipedia entry - AmigaOS port - error?

[Tom Lane]

Glyn Astill  writes:
> Just noticed on the wikipedia page under rdbms, it lists postgresql as 
> available on AmigaOS.

> http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems

> Is this just an error, as I see edb advanced server is also listed as 
> available, or was there some working code at some point in time?

I think it's probably bogus.  There's some mention in very old versions
of the ports list of being able to run on top of NetBSD on Amiga
hardware.  But unless somebody had code to duplicate the Unix syscall set
on AmigaOS, there'd have been no way to make PG run on that.

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] [HACKERS] Postgres 9.1 - Release Theme

[Scott Marlowe]

On Thu, Apr 1, 2010 at 10:05 AM, David E. Wheeler  wrote:
> On Apr 1, 2010, at 3:01 AM, Magnus Hagander wrote:
>
>>> I prefer to dump all my data in a big text file and grep it for the 
>>> information I need.
>>
>> As long as you implement your own grep, that sounds about on par with
>> the current trends! Go for it!
>
> Well, first you have to implement your own compiler. Also a lexer and a 
> parser.

All that will be for naught unless you hand wire your own logic
boards.  I mean really, come on.

-- 
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] "1-Click" installer problems

[John Gage]

This is of interest to me, because something similar happens on the  
Mac (with the one-click installer).


The data directory is placed in a file that user 'postgres' has  
permission on.  Of course, this is a new user, created by postgres,  
but what it ends up meaning is that I run all my postgres files under  
root instead of in my user directory.


Now, I know that is stupid, cludgy, and probably un-American, but it  
was the simplest work-around I came up with.


I wonder if the problems arise because user 'postgres' is being  
created on the Windows machine and no one expects him, knows he's  
there, or has ever met him?


John


On Apr 1, 2010, at 9:09 AM, Thomas Kellerer wrote:


Nikhil G. Daddikar, 01.04.2010 08:04:


In about 30 seconds I found the following unanswered threads  
relating to
installation on Windows Vista. If anybody is interested I can find  
more.


The problem with this kind of statistics is that you will only find  
people who complain, you'll never find people who do not complain  
because they have no problems. Actually that's true for all internet  
forums or mailing lists: you'll seldomly find people posting  
something like  "Hey everything works fine, I had no problems".


All the posts seem to share the same root cause: the data directory  
has been put into "c:\Program Files" but a regular user does not  
have write permissions on that directory. As the installer is  
usually run with Administrator rights, the directory can be created  
but the service (or initdb) runs under a normal user account that  
cannot write to that directory because.


I do not like the installer's suggestion to put the data directory  
into c:\Program Files either, I think this should default to %APPDATA 
% instead of %ProgramFile%. I bet half of the problems would go away  
if the installer refused to put the data directory into c:\Program  
Files.


Given the fact that Microsoft finally tries to enforce people not to  
work as Administrators makes this even more important.


My suggestion is to try to use a different data directory when  
installing Postgres and make sure that the postgres service account  
is allowed to read and write that directory.


Personally I switched to using the ZIP packages completely because  
it is so much easer (unzip, initdb, pg_ctl -register, done)


Thomas




--
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: [HACKERS] [GENERAL] Postgres 9.1 - Release Theme

[Dave Page]

2010/4/1  :
>> Following a great deal of discussion, I'm pleased to announce that the
>> PostgreSQL Core team has decided that the major theme for the 9.1
>> release, due in 2011, will be 'NoSQL'.
>>
>
> Please, provide me your address so I can forward you the "health care"
> bills I had to pay due to the heart attack I suffered this morning (when
> reading your post).

:-p

> BTW PostgreSQL core team is not alone realizing how obsolete relational
> databases are:
> http://thedailywtf.com/Articles/Announcing-APDB-The-Worlds-Fastest-Database.aspx

Yeah, I read that earlier in my daily lunchtime jaunt over to the
Daily WTF. Alex clearly spent more time on his text than I did!



-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Anyone using RubyRep replication?

[Ozz Nixon]

Hello,

I am looking to implemented a master-master replication solution 
for an Internet Provider company using PostgreSQL. I am looking at 
RubyRep, but wanted to know if any of you are really using the product 
in production and could share any pros/cons? Or is there another 
solution I should be looking at for true master-master?


Thanks,
O.

--
Thank you,

G.E. Ozz Nixon
CEO/Sr. Software Architect
3F, LLC
125 Robin Dr.
Barto PA 19504
Office: 1-484-363-2304
Cell: 1-610-698-7976
Email: o...@3flabs.com

The information contained in this communication is confidential. It is intended 
only for the use of the recipients named above, and may be legally privileged. 
If the reader of this message is not the intended recipient, you are here by 
notified that any dissemination, distribution or copying of this communication 
is strictly prohibited.

If you have received this communication in error, please resend the 
communication to the sender and delete the original message or any copy of it 
from your computer systems.


--
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] [HACKERS] Postgres 9.1 - Release Theme

[David E. Wheeler]

On Apr 1, 2010, at 3:01 AM, Magnus Hagander wrote:

>> I prefer to dump all my data in a big text file and grep it for the 
>> information I need.
> 
> As long as you implement your own grep, that sounds about on par with
> the current trends! Go for it!

Well, first you have to implement your own compiler. Also a lexer and a parser.

David


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Wikipedia entry - AmigaOS port - error?

[Glyn Astill]

Hi Chaps,

Just noticed on the wikipedia page under rdbms, it lists postgresql as 
available on AmigaOS.

http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems

Is this just an error, as I see edb advanced server is also listed as 
available, or was there some working code at some point in time?

I'm just merely amused/interested, I've got a stupidly modified Amiga somewhere 
from my teenage years, but I doubt I'll be pulling it out to try.

Glyn






-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres 9.1 - Release Theme

[tv]

> Following a great deal of discussion, I'm pleased to announce that the
> PostgreSQL Core team has decided that the major theme for the 9.1
> release, due in 2011, will be 'NoSQL'.
>

Please, provide me your address so I can forward you the "health care"
bills I had to pay due to the heart attack I suffered this morning (when
reading your post).

BTW PostgreSQL core team is not alone realizing how obsolete relational
databases are:
http://thedailywtf.com/Articles/Announcing-APDB-The-Worlds-Fastest-Database.aspx

Tomas


-- 
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] transaction control in pl/pgsql

[Alban Hertroys]

On 1 Apr 2010, at 14:38, Birgit Laggner wrote:

> Hi Alban,
> 
> thanks for your detailed answer!
> 
> My database settings:
> max connections: 20
> work_mem: 100MB
> shared buffers: 12000MB
> 
> Server memory:
> physical 32GB
> total memory 50GB (incl. swap)
> shared memory ??

Ok, so max work_mem in use at any given time is 20 * 100MB ~ 2G. Considering 
you have 12G shared buffers and 50G available in total that's not your problem.

Btw, the amount of shared memory is something controlled from the kernel (you 
appear to be on a Linux system). If less than your 12G shared buffers would 
have been configured for kernel shared memory, then Postgres wouldn't start up 
at all, so there's probably no need to look at its value.

> I am not sure if I use deferred constraints - the only constraints I use
> in the function are NOT NULL constraints. But I don't believe my memory
> overflow had something to do with them, because the error occured during
> a loop. There should not happen any sudden changes regarding any NOT
> NULL constraints between one loop cycle and the next.

Deferred constraints are constraints that aren't checked until the end of the 
transaction. You can imagine keeping track of the things to check on commit can 
build up. But this doesn't apply to NOT NULL constraints as the documentation 
says:

DEFERRABLE
NOT DEFERRABLE
This controls whether the constraint can be deferred. A constraint that is not 
deferrable will be checked immediately after every command. Checking of 
constraints that are deferrable may be postponed until the end of the 
transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. 
Only foreign key constraints currently accept this clause. All other constraint 
types are not deferrable.


> My function was the only connection to the database as far as I know -
> so, I don't think allocation of memory should be a reason...

Probably not, no.

> I would say: Yes, my function seems to store large amounts of data in
> memory. But in my function, I tried to store as much as possible of the
> interim results in real tables (not temp tables) instead of storing them
> in variables.

Good practice I think. Do any of those variables ever contain a large amount of 
data? I realised that since you have one single function many of your variables 
stay allocated until the final END statement. If that's what's causing you to 
run out of memory then it would help to split the function up into smaller 
ones, it would reduce the scope of such variables.

> But my guess is that postgres doesn't write the tables and
> therefore keeps everything in memory. (by the way: the swap was used up,
> too)

No it certainly doesn't do that, if you're operating on tables (like you say 
you do) then it writes such things to its WAL.

Maybe there's something else trying to keep lots of data in memory? Can you 
tell what is using all that memory?

What happens to the results of your function? Is that a large result set and is 
some piece of software on the same machine buffering all of that in memory, to 
display it for example? I recall psql can suffer from that problem, but it has 
a switch to turn that off (the name eludes me). Web-site scripts also have a 
tendency to handle data that way.

A few wild guesses; Did someone for example configure the WAL to be on a 
memory-disk? Is some other process churning up memory while you're running your 
function?

> It's really difficult to post only a part of the function, just because
> every next step is based on the result of the previous step. I also
> guess that every step on its own wouldn't cause memory overflow, but
> that it's more like the memory use adds up with every step. But I will
> try and cut the function into little snippets and let them run one for
> one - perhaps the memory overflows still occurs for one snippet...
> 
> I you have any ideas ...
> 
> Thanks again and regards,
> 
> Birgit.
> 
> 
> 
> 
> On 01.04.2010 13:27, Alban Hertroys wrote:
>> On 1 Apr 2010, at 12:22, Birgit Laggner wrote:
>> 
>> 
>>> Dear list,
>>> 
>>> I have some data (big size) and I've written a long function in pl/pgsql
>>> which processes the data in several steps. At a test run my function
>>> aborted because of memory exhaustion. My guess is, that everything what
>>> happens during the function transaction is stored in the memory - until
>>> it's full...
>>> 
>> No, transactions operate on the database just like anything else. Running 
>> out of memory doing that is not impossible, but you'd need to create some 
>> really big transactions to reach those limits (I think I've been quoted the 
>> limit being 2^32 instructions per transaction a looong time ago, just to 
>> give you an indication).
>> 
>> You're probably running out of memory for another reason. Now to figure out 
>> why...
>> 
>> I've seen an issue with deferred constraints causing this, for example. Do 
>> you use deferred constraints?
>> 
>> Another cause that pops up regularly is

Re: [GENERAL] [Solved] 8.3 Stats Collector Stuck at 100% CPU

[Tom Lane]

Josh Kupershmidt  writes:
> The solution for me was simply to run pg_stat_reset(), and the problem
> went away within a few seconds. The $PGDATA/global/pgstat.stat file
> was 1.2GB before the reset, and went down to ~250KB after the reset.
> It looks like this file is adding on 4MB or so per day at the current
> rate, so I'll likely have to do this again in a few months.

Hm.  It sounds like you are "leaking" stats collector table entries for
some reason.  It would be good to fix the underlying problem rather than
just resign yourself to a manual workaround.  Is there anything unusual
about your workload that might trigger this?

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] simultaneously reducing both memory usage and runtime for a query

[Alvaro Herrera]

Faheem Mitha wrote:

> Regardless, you don't address why the email I sent disappeared into
> the ether. If it was rejected due to excessive length, then it would
> be polite to get an automated rejection. Perhaps something like
> 
> "Your message is too long and so is being rejected. Messages to the
> ... mailing list need to be under x lines"

Our mailing lists are set up with a spam filter that's very happy to
drop stuff on the floor without warning at first suspicion.  It's not an
easy thing to fix for reasons that are outside the scope of this mailing
list.

-- 
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] pgreplay log file replayer released

[Greg Smith]

I just summarized some of the discussion on this thread and created a 
wiki page that starts to cover each of the three tools now available for 
this job:  http://wiki.postgresql.org/wiki/Statement_Playback


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Performance statistics

[Michael Gould]

Without asking for any blood letting, I'm wondering if there are any hard
statistics available to prove  if  Windows Server is faster than, slower
than or the same as for performance to one of the various Linux
distributions.  While our application is a commerical application, in our
survey we've been asked for information on running the server on a Linux box
vs a Windows Server.


I suspect that running on a Linux server will be faster, however I'm
concerned about maintenance at customer sites who have no Linux support and
are a Windows based shop.  The application is written with a Windows based
language. We will also be hosting smaller companies so I want to see the
statistics and if Linux is a clear winner, the best distrubution to work
with.


 


Best Regards


--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax

Re: [GENERAL] transaction control in pl/pgsql

[Birgit Laggner]

Hi Alban,

thanks for your detailed answer!

My database settings:
max connections: 20
work_mem: 100MB
shared buffers: 12000MB

Server memory:
physical 32GB
total memory 50GB (incl. swap)
shared memory ??

I am not sure if I use deferred constraints - the only constraints I use
in the function are NOT NULL constraints. But I don't believe my memory
overflow had something to do with them, because the error occured during
a loop. There should not happen any sudden changes regarding any NOT
NULL constraints between one loop cycle and the next.

My function was the only connection to the database as far as I know -
so, I don't think allocation of memory should be a reason...

I would say: Yes, my function seems to store large amounts of data in
memory. But in my function, I tried to store as much as possible of the
interim results in real tables (not temp tables) instead of storing them
in variables. But my guess is that postgres doesn't write the tables and
therefore keeps everything in memory. (by the way: the swap was used up,
too)

It's really difficult to post only a part of the function, just because
every next step is based on the result of the previous step. I also
guess that every step on its own wouldn't cause memory overflow, but
that it's more like the memory use adds up with every step. But I will
try and cut the function into little snippets and let them run one for
one - perhaps the memory overflows still occurs for one snippet...

I you have any ideas ...

Thanks again and regards,

Birgit.




On 01.04.2010 13:27, Alban Hertroys wrote:
> On 1 Apr 2010, at 12:22, Birgit Laggner wrote:
>
>   
>> Dear list,
>>
>> I have some data (big size) and I've written a long function in pl/pgsql
>> which processes the data in several steps. At a test run my function
>> aborted because of memory exhaustion. My guess is, that everything what
>> happens during the function transaction is stored in the memory - until
>> it's full...
>> 
> No, transactions operate on the database just like anything else. Running out 
> of memory doing that is not impossible, but you'd need to create some really 
> big transactions to reach those limits (I think I've been quoted the limit 
> being 2^32 instructions per transaction a looong time ago, just to give you 
> an indication).
>
> You're probably running out of memory for another reason. Now to figure out 
> why...
>
> I've seen an issue with deferred constraints causing this, for example. Do 
> you use deferred constraints?
>
> Another cause that pops up regularly is that people specify too much global 
> work_mem for postgres. work_mem gets allocated per connection, so the more 
> connections you have the more memory goes to work_mem and other resources may 
> receive too little, or you cross what's available.
> What are your relevant postgres settings (max connections, work_mem, shared 
> mem, etc.) and how much physical, total and shared memory does your server 
> have?
>
> Another possibility is that your function stores large amounts of data in 
> variables that are not backed up by database tables. That means all that data 
> will be stored in memory, and even if it goes to swap at some point (not sure 
> it will, it would seriously hurt performance) there is a limit.
> If this is the case, maybe you could use temporary tables to process that 
> data instead of trying to do it all in memory.
>
>   
>> So, my idea for solving that problem would be to cut the
>> big function into smaller functions. But, I don't want to write 30
>> function calls at the end - I would rather like to have one function
>> which is calling all these small functions, so I would only have to
>> write one sql-query at the end.
>> 
> Splitting up big functions into smaller functions is always a good idea. 
> That's part of general programming paradigms. It won't cut down the size of 
> your transaction though.
>
>   
>> What I fear is either, that, if this
>> function calls the other functions, everything is only one trancaction
>> again and I get memory overflow once more.
>> 
>
> If the problem persists, maybe you could post your function somewhere. As 
> it's apparently a rather long function, can you strip it down to something 
> that still causes it to run out of memory but that will be a bit easier for 
> the people on this list to wade through?
>
> Alban Hertroys
>
> --
> If you can't see the forest for the trees,
> cut the trees and you'll see there is no forest.
>
>
> !DSPAM:1054,4bb4832810417514219450!
>
>
>
>   

-- 
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] PgCluster

[Thom Brown]

On 1 April 2010 13:57, mike stanton  wrote:

>  Hello all, this is not a joke. Does anyione know if PgCluster as a
> project is suspended, or is alive and kicking. In the second case, has
> anyone got a medium sized system working well with this multi-master
> replication option.?
>
> Hope to hear soon.
> Mike Stanton Santiago, Chile
>

It's ground to a halt and is no longer under active development as far as
I'm aware.

Thom

Re: [GENERAL] "1-Click" installer problems

[Massa, Harald Armin]

Dave,

> >
> > IIRC, that was modeled on where Microsofts own SQL Server put it's
> > data files by default. Does anybody know if that has changed recently?
>
> It hasn't. I checked 2008 this morning.
>
>
And how does SQL Server fiddle that with Windows 7? My experience is that W7
is "not amused" when trying to put data below Programs and Files (x86), as I
described within:

http://archives.postgresql.org/pgsql-general/2009-01/msg00783.php

The default as of know is \ProgramData\



Best wishes,

Harald

-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
%s is too gigantic of an industry to bend to the whims of reality

Re: [GENERAL] postgres vs mysql conventional wisdom

[Thom Brown]

On 1 April 2010 12:21, Ron Mayer  wrote:

>
> Broad Developer base - Advantage MySQL for having a more distributed team.
>
>  Postgres developers seem to be concentrated in a handful of companies.
>  After various acquisitions, MySQL developers are scattered to the four
> winds.
>

You mean EnterpriseDB, Red Hat, F-Secure, Command Prompt, 2nd Quadrant,
PostgreSQL Experts, Redpill, Credativ, Afilias, End Point, Google, NTT but
to name a few?

[GENERAL] PgCluster

[mike stanton]

Hello all, this is not a joke. Does anyione know if PgCluster as a project is 
suspended, or is alive and kicking. In the second case, has anyone got a medium 
sized system working well with this multi-master replication option.?

Hope to hear soon.
Mike Stanton Santiago, Chile

Re: [GENERAL] "1-Click" installer problems

[Dave Page]

On Thu, Apr 1, 2010 at 12:40 PM, Massa, Harald Armin  wrote:
> Dave,
>>
>> >
>> > IIRC, that was modeled on where Microsofts own SQL Server put it's
>> > data files by default. Does anybody know if that has changed recently?
>>
>> It hasn't. I checked 2008 this morning.
>>
>
> And how does SQL Server fiddle that with Windows 7? My experience is that W7
> is "not amused" when trying to put data below Programs and Files (x86), as I
> described within:
> http://archives.postgresql.org/pgsql-general/2009-01/msg00783.php

I have no idea how SQL Server does it. There was a minor amount of
magic required to get our installers to work without Windows trying to
redirect the writes, but I forget what that was exactly now.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

-- 
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] "1-Click" installer problems

[Dave Page]

On Thu, Apr 1, 2010 at 12:35 PM, Nikhil G. Daddikar  wrote:

> All I am saying is that the folks check out what the diff between EDB and
> the "old" installer is without jumping to conclusions about APPDATA or
> whatever because the old installer worked. Maybe it is a fluke but isn't it
> worth investigating in order to fix the installer?

The old installer used entirely different technology, which is not
easily comparable. It was also never tested on Windows 7.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

-- 
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] "1-Click" installer problems

[Nikhil G. Daddikar]

Peter,


Are you sure about that? Have you actually tested that? Even if you
find that the old 8.3 pginstaller works where the EDB installer
failed, that doesn't actually demonstrate that pginstaller is
inherently superior to the EDB installer. It is likely due to a fluke.
Perhaps it is down to something vestigial from pginstaller remaining.


All I am saying is that the folks check out what the diff between EDB 
and the "old" installer is without jumping to conclusions about APPDATA 
or whatever because the old installer worked. Maybe it is a fluke but 
isn't it worth investigating in order to fix the installer?



Your incredible sense of entitlement is very irritating. Your unctuous
statement that "Evangelizing PGSQL was a mistake", as if you expect a
contrite letter, is just too much to bear.
   You're the one with the big ego. I think that people's responses so
far have been very subdued, considering how obnoxious you've been. In
your first e-mail, you called the EDB one click installer "a scam".


Sometimes we say things that we don't actually mean and this was one of 
them. I understand I have hurt some folks and I ask forgiveness from all 
of them.


I look forward to working with EDB and help them in fixing the issues.

-n.

--
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] transaction control in pl/pgsql

[Alban Hertroys]

On 1 Apr 2010, at 12:22, Birgit Laggner wrote:

> Dear list,
> 
> I have some data (big size) and I've written a long function in pl/pgsql
> which processes the data in several steps. At a test run my function
> aborted because of memory exhaustion. My guess is, that everything what
> happens during the function transaction is stored in the memory - until
> it's full...

No, transactions operate on the database just like anything else. Running out 
of memory doing that is not impossible, but you'd need to create some really 
big transactions to reach those limits (I think I've been quoted the limit 
being 2^32 instructions per transaction a looong time ago, just to give you an 
indication).

You're probably running out of memory for another reason. Now to figure out 
why...

I've seen an issue with deferred constraints causing this, for example. Do you 
use deferred constraints?

Another cause that pops up regularly is that people specify too much global 
work_mem for postgres. work_mem gets allocated per connection, so the more 
connections you have the more memory goes to work_mem and other resources may 
receive too little, or you cross what's available.
What are your relevant postgres settings (max connections, work_mem, shared 
mem, etc.) and how much physical, total and shared memory does your server have?

Another possibility is that your function stores large amounts of data in 
variables that are not backed up by database tables. That means all that data 
will be stored in memory, and even if it goes to swap at some point (not sure 
it will, it would seriously hurt performance) there is a limit.
If this is the case, maybe you could use temporary tables to process that data 
instead of trying to do it all in memory.

> So, my idea for solving that problem would be to cut the
> big function into smaller functions. But, I don't want to write 30
> function calls at the end - I would rather like to have one function
> which is calling all these small functions, so I would only have to
> write one sql-query at the end.

Splitting up big functions into smaller functions is always a good idea. That's 
part of general programming paradigms. It won't cut down the size of your 
transaction though.

> What I fear is either, that, if this
> function calls the other functions, everything is only one trancaction
> again and I get memory overflow once more.


If the problem persists, maybe you could post your function somewhere. As it's 
apparently a rather long function, can you strip it down to something that 
still causes it to run out of memory but that will be a bit easier for the 
people on this list to wade through?

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4bb4831310417247659380!



-- 
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 vs mysql conventional wisdom

[Ron Mayer]

On this first day of the month, I thought it might be interesting
to re-visit the conventional wisdom about postgres vs mysql.

Do these seem like fair observations?


Storage engines - Advantage Postgres for having far more available.

 Postgre has such a wide range of storage engines to choose from --
 ranging from whatever proprietary backend Yahoo uses,
 to Fujitsu's proprietary storage engine; to PowerGres Plus's
 proprietary storage engine; to Netezza's hardware accelerated
 storage engine.  In contrast, MySQL only has a few to choose from.

Broad Developer base - Advantage MySQL for having a more distributed team.

 Postgres developers seem to be concentrated in a handful of companies.
 After various acquisitions, MySQL developers are scattered to the four winds.

Large company support - Advantage Postgres for having larger companies support 
it.

 Fujitsu ($46 billion 2009 revenue; 185,000
 employees) much bigger than that little company  that bought
 whomever bought MySQL ($23 billion revenue; 73000 employees).

Any more?

-- 
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] "1-Click" installer problems

[Dave Page]

On Thu, Apr 1, 2010 at 12:15 PM, Peter Geoghegan
 wrote:
> I think that the EDB one click installer for windows is a great piece
> of software, and I'm very grateful to Dave and the EDB people for
> maintaining it, as well as the stack builder that it comes with.

Thank you Peter - we appreciate the thought.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

-- 
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] "1-Click" installer problems

[Peter Geoghegan]

> It is not the count that matters. It is the quality. Even after giving logs,
> your folks are unable to figure out the problem. I am facing the same
> problem that was faced by a lot of users on the forum and not one was
> resolved successfully. Otherwise I would've moved ahead on my own. I am
> interested in fixing the installer. I am not interested in manual steps to
> get it working on my PC because there are a hundred other PCs that I have to
> worry about and a fix in the installer would be right step ahead. But
> someone has to admit that there is a problem in the installer. All arguments
> in this thread are junk because 8.3 installer for win32 from postgresql.org
> WORKS using the same conditions on Vista/2003/7 everywhere. It is nothing to
> do with APPDATA or any such thing. Something that worked was broken, it's
> that simple. And nobody wants to know what was.

Are you sure about that? Have you actually tested that? Even if you
find that the old 8.3 pginstaller works where the EDB installer
failed, that doesn't actually demonstrate that pginstaller is
inherently superior to the EDB installer. It is likely due to a fluke.
Perhaps it is down to something vestigial from pginstaller remaining.

Your incredible sense of entitlement is very irritating. Your unctuous
statement that "Evangelizing PGSQL was a mistake", as if you expect a
contrite letter, is just too much to bear.

You're the one with the big ego. I think that people's responses so
far have been very subdued, considering how obnoxious you've been. In
your first e-mail, you called the EDB one click installer "a scam".

I think that the EDB one click installer for windows is a great piece
of software, and I'm very grateful to Dave and the EDB people for
maintaining it, as well as the stack builder that it comes with. I
think we could all do without hearing your idle whining and conspiracy
theories about the PostgreSQL global development group having an
anti-windows bias. In fact, the PostgreSQL devs regularly bend over
backwards to support windows.

Regards,
Peter Geoghegan

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Large Object leakage

[Harald Fuchs]

I have a DB (mydb) where one table (mytbl) contains a large object
column.  The contents are managed by contrib/lo.  This breaks when I
want to copy the DB to another host where the schema is already
present with some old contents:  when I do

  pg_dump -c mydb | psql -q -h otherhost mydb -f -

pg_dump emits a "DROP TABLE mytbl" which leaves the old lo contents on
otherhost orphaned and, even worse, raises an exception if pg_dump
wants to create a large object with an id already present.  I thought
about a TRUNCATE TRIGGER which could make the appropriate lo_unlink
calls, but this trigger won't be called by DROP TABLE.

Any ideas?


-- 
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] prevent connection using pgpass.conf

[Alban Hertroys]

On 1 Apr 2010, at 11:21, Christophe Dore wrote:

> Thanks for answering
> 
> Yes, you are right. This is a client-side file. However, our concern is
> that we have to consider this practice as a security issue. We'd like to
> ban this practice for our product which is, thus, wrapping PostgresQL
> engine. Thus my questions
> 
> - is there any configuration that can be done on server side to prevent
> the client side to use such file to read passwords ?
> - is there any options that can be set in postgres libpq C library to
> prevent the connection functions to search for password in files ?


Nothing prevents a user from creating such files, regardless whether the server 
accepts the information in it or not. I get the impression you want to prevent 
passwords being stored in files on user systems - probably thinking that if 
such a file were 'stolen' then someone could access your database and possibly 
modify things.

Although this is basically true, there is no way you can prevent users from 
storing passwords on their computers. If they're not put in .pgpass files there 
will be users who store them unencrypted in text files conveniently named 
'passwords' in their home directories. They'll probably do that anyway.

>From the server side there's nothing you can do about that, so not accepting 
>data from .pgpass files will hardly help you.

I have to say I was a bit surprised to find that .pgpass files store those 
passwords as plain text though. Some method like ssh uses with public and 
private keys would be an improvement IMO. Especially since we can choose to use 
password encryption over the wire.

Storing those passwords encrypted on the client side seems the proper way to 
deal with this issue. IMHO, time working on that is better spent than time 
trying to prevent .pgpass files from working.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4bb47e3510419564511622!



-- 
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] transaction control in pl/pgsql

[Grzegorz Jaśkiewicz]

2010/4/1 Birgit Laggner 

> Hi Grzegorz,
>
> sorry, but that doesn't help me, perhaps you could get a little bit
> clearer:
>
> @a) Does the use of SAVEPOINT avoid memory overflow? I could not find an
> explanation about memory use in the documentation of SAVEPOINT.
>
> transactions don't really use a lot of memory, but you want to keep them
short, due to possible locking, etc.


> @b) Do you mean I should not process my data or I should not use plpgsql
> to do that? In what way I'm trying to outsmart the software???
>
>
you are trying to save some memory, that database is going to allocated. You
can control that much better by writing things the way others do, and by
tweaking your config.

What I was trying to say in a), is that you can control transactions in a
way - by using savepoints.

You started to talk about saving memory that database might allocate for
transactions. This sounds like 'I am trying to be smarter about things than
my DB'. Don't do that. It is good to know how things work, and use it
wisely, but don't do things for database, since it was designed to take care
of memory, and transactions, etc.




-- 
GJ

Re: [GENERAL] transaction control in pl/pgsql

[Birgit Laggner]

Hi Grzegorz,

sorry, but that doesn't help me, perhaps you could get a little bit clearer:

@a) Does the use of SAVEPOINT avoid memory overflow? I could not find an
explanation about memory use in the documentation of SAVEPOINT.

@b) Do you mean I should not process my data or I should not use plpgsql
to do that? In what way I'm trying to outsmart the software???

Thanks,

Birgit.


On 01.04.2010 12:35, Grzegorz Jaśkiewicz wrote:
> a) you can't explicitly control transactions in plpgsql. If you need
> some sort of a form of it, use save points.
> b) you are trying to outsmart database software, and this is just a
> biiig mistake, and you should stop doing that completely.
>

-- 
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] "1-Click" installer problems

[Nikhil G. Daddikar]

Folks,

I received a call from EnterpriseDB trying to understand what the 
problem is. We are scheduled to meet on Monday. I will post the findings 
as soon as I have something concrete.


Thank you all.
-n.


Unless you are prepared to help us understand exactly what is unique
about your systems so we can figure out what is going wrong, then we
cannot help you.
   



--
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] transaction control in pl/pgsql

[Pavel Stehule]

2010/4/1 Birgit Laggner :
> Dear list,
>
> I have some data (big size) and I've written a long function in pl/pgsql
> which processes the data in several steps. At a test run my function
> aborted because of memory exhaustion. My guess is, that everything what
> happens during the function transaction is stored in the memory - until
> it's full... So, my idea for solving that problem would be to cut the
> big function into smaller functions. But, I don't want to write 30
> function calls at the end - I would rather like to have one function
> which is calling all these small functions, so I would only have to
> write one sql-query at the end. What I fear is either, that, if this
> function calls the other functions, everything is only one trancaction
> again and I get memory overflow once more.

plpgsql can you implicit subtransaction - every block with protected
section is evaluated under subtransaction. But I don't think, so
subtransaction help in your case. You do some what is memory expensive
- example: larger SRF function in plpgsql, badly used hash
aggregation, maybe using of deffered triggers. Subtransaction doesn't
help. Try to use temp tables instead.

Regards
Pavel Stehule

>
> I've read the documentation regarding this on
> http://www.postgresql.org/docs/8.4/interactive/plpgsql-structure.html :
>
> "It is important not to confuse the use of BEGIN/END for grouping
> statements in PL/pgSQL with the similarly-named SQL commands for
> transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do
> not start or end a transaction. Functions and trigger procedures are
> always executed within a transaction established by an outer query —
> they cannot start or commit that transaction, since there would be no
> context for them to execute in."
>
> Somewhere else I've read: "PostgreSQL does not have nested transactions."
>
> I'm still not sure if I got it right or if there are other possibilities
> to solve my problem. Any suggestions would be appreciated!
>
> Thanks and regards,
>
> Birgit.
>
> --
> 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] transaction control in pl/pgsql

[Grzegorz Jaśkiewicz]

a) you can't explicitly control transactions in plpgsql. If you need some
sort of a form of it, use save points.
b) you are trying to outsmart database software, and this is just a biiig
mistake, and you should stop doing that completely.

[GENERAL] transaction control in pl/pgsql

[Birgit Laggner]

Dear list,

I have some data (big size) and I've written a long function in pl/pgsql
which processes the data in several steps. At a test run my function
aborted because of memory exhaustion. My guess is, that everything what
happens during the function transaction is stored in the memory - until
it's full... So, my idea for solving that problem would be to cut the
big function into smaller functions. But, I don't want to write 30
function calls at the end - I would rather like to have one function
which is calling all these small functions, so I would only have to
write one sql-query at the end. What I fear is either, that, if this
function calls the other functions, everything is only one trancaction
again and I get memory overflow once more.

I've read the documentation regarding this on
http://www.postgresql.org/docs/8.4/interactive/plpgsql-structure.html :

"It is important not to confuse the use of BEGIN/END for grouping
statements in PL/pgSQL with the similarly-named SQL commands for
transaction control. PL/pgSQL's BEGIN/END are only for grouping; they do
not start or end a transaction. Functions and trigger procedures are
always executed within a transaction established by an outer query —
they cannot start or commit that transaction, since there would be no
context for them to execute in."

Somewhere else I've read: "PostgreSQL does not have nested transactions."

I'm still not sure if I got it right or if there are other possibilities
to solve my problem. Any suggestions would be appreciated!

Thanks and regards,

Birgit.

-- 
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] "1-Click" installer problems

[Thomas Kellerer]

Magnus Hagander, 01.04.2010 11:50:

2010/4/1 Craig Ringer:

instead of %ProgramFile%. I bet half of the problems would go away if
the installer refused to put the data directory into c:\Program Files.


Yep - it's not a clever place to put it.


IIRC, that was modeled on where Microsofts own SQL Server put it's
data files by default.



Shouldn't Postgres make it better than Microsoft ;)



--
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] "1-Click" installer problems

[Dave Page]

On Thu, Apr 1, 2010 at 10:50 AM, Magnus Hagander  wrote:
> 2010/4/1 Craig Ringer :
>>> instead of %ProgramFile%. I bet half of the problems would go away if
>>> the installer refused to put the data directory into c:\Program Files.
>>
>> Yep - it's not a clever place to put it.
>
> IIRC, that was modeled on where Microsofts own SQL Server put it's
> data files by default. Does anybody know if that has changed recently?

It hasn't. I checked 2008 this morning.


-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

-- 
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] "1-Click" installer problems

[Magnus Hagander]

2010/4/1 Craig Ringer :
>> instead of %ProgramFile%. I bet half of the problems would go away if
>> the installer refused to put the data directory into c:\Program Files.
>
> Yep - it's not a clever place to put it.

IIRC, that was modeled on where Microsofts own SQL Server put it's
data files by default. Does anybody know if that has changed recently?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

-- 
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] prevent connection using pgpass.conf

[Magnus Hagander]

2010/4/1 Christophe Dore :
>
> - is there any configuration that can be done on server side to prevent
> the client side to use such file to read passwords ?

No. It happens before the user ha slogged in, obviously.


> - is there any options that can be set in postgres libpq C library to
> prevent the connection functions to search for password in files ?

No, but you could possibly set the PGPASSFILE environment variable to
a place where you *know* there won't be a pgpass file, if you can
determine such a place.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Re : Re : Re : Re : [GENERAL] Select in temporary table

[Alban Hertroys]

On 1 Apr 2010, at 10:47, Sylvain Lara wrote:
> De : jose javier parra sanchez 
> Cc : pgsql-general@postgresql.org
> Envoyé le : Jeu 1 avril 2010, 9 h 26 min 27 s
> Objet : Re: Re : Re : Re : [GENERAL] Select in temporary table
> 
> Not sure about how de C# driver you use to connect, but if you have
> multiple connections, then you have multiple sessions.
> 
> ---
> 
> I've got multiple connections, but when selecting data in my temp table (when 
> it works, once, twice, three times and more before the error), I've got my 
> data when selecting into the temp table.
> 
> Whereas if I connect the same base, at the same time, from another client, it 
> doesn't display the same data, which is correct for me.
> 
> Each client has it's own temporary table data.
> 
> But my problem is that this temporary table is randomly accessible by the 
> same client.
> And it's seems that if I wait a moment between two executions (I put a 
> breakpoint and browse my NpgSQLConnection object), I can access my temp table 
> without any problem...


Sounds like there's a connection pooler in there somewhere and you sometimes 
get a different connection than the one your temp table lives in. Only the 
connection that created the temp table will be able to see it.

Other possibilities I can think of:

Are you rolling back the transaction with which you created the temp table? In 
that case the temp table is gone after you rolled back, so if later code is 
trying to access it it won't find it.

Is your client multi-threaded? Is it possible that you're trying to access the 
temp table from another thread before it finished creating?
That's probably not the case, or you'd either need to serialize your database 
access to be able to use the same connection - in which case this situation 
can't occur - or you're using multiple parallel connections from the same 
client and we're back at the multiple-connection answer. If you'd be using one 
connection in parallel threads without serializing them then you're having more 
serious issues to deal with...

It's most probably a problem in your client or your connection library, other 
people are using temp tables without a problem. Temp tables are not exactly a 
new feature either.

Alban Hertroys

PS. Considering the way you quote your mails you seem to have an absolutely 
terrible mail client. If you have a choice, consider getting something decent.

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4bb46f1610411569890503!



-- 
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] [HACKERS] Postgres 9.1 - Release Theme

[Magnus Hagander]

2010/4/1 Thom Brown :
> On 1 April 2010 09:13, Dave Page  wrote:
>>
>> Following a great deal of discussion, I'm pleased to announce that the
>> PostgreSQL Core team has decided that the major theme for the 9.1
>> release, due in 2011, will be 'NoSQL'.
>>
>> There is a growing trend towards NoSQL databases, with major sites
>> like Twitter and Facebook utilising them extensively. NoSQL databases
>> often include multi-master replication, clustering and failover
>> features that have long been requested in PostgresSQL, but have been
>> extremely difficult to implement with SQL which has prevented us from
>> advancing Postgree in the way that we'd like.
>>
>> To address this, the intention is to remove SQL support from
>> Postgres, and replace it with a language called 'QUEL'. This will
>> provide us with the flexibility we need to implement the features of
>> modern NoSQL databases. With no SQL support there will obviously be
>> some differences in the query syntax that must be used to access your
>> data. For example, the query:
>>
>> select (e.salary/ (e.age - 18)) as comp from employee as e where
>> e.name = "Jones"
>>
>> would be rewritten as:
>>
>> range of e is employee retrieve (comp = e.salary/ (e.age - 18)) where
>> e.name = "Jones"
>>
>> Aggregate syntax in QUEL is particularly powerful. For example, the query:
>>
>> select dept,
>>      avg(salary) as avg_salary,
>>      sum(salary) as tot_salary
>> from
>>      employees
>> group by
>>      dept
>>
>> may be written as:
>>
>> range of e is employee
>> retrieve (e.dept,
>>         avg_salary = avg(e.salary by e.dept),
>>         tot_salary = sum(e.salary by e.dept)
>> )
>>
>> Note that the grouped column can be specified for each individual
>> aggregate.
>>
>> We will be producing a comprehensive guide to the QUEL syntax to aid
>> with application migration. We appreciate the difficulty that this
>> change may cause some users, but feel we must embrace the NoSQL
>> philosophy in order to remain "The world's most advanced Open Source
>> database"
>>
>> "There's no question that, at 21 years old, the SQL standard is past its
>> prime," said core developer and standards expert Peter Eisentraut. "It's
>> time for us to switch to something fresher.  I personally would have
>> preferred XSLT, but QUEL is almost as good."
>>
>> Project committer Heikki Linnakangas added: "By replacing SQL with
>> QUEL not only will will be able to add new features to Postgres that
>> were previously too difficult, but we'll also increase user loyalty as it'll
>> be much harder for them to change to a different, SQL-based
>> database. That'll be pretty cool."
>>
>> You may also notice that without SQL, the project name is somewhat
>> misleading. To address that, the project name will be changed to
>> 'PostgreQUEL' with the 9.1 release. We expect this will also put an
>> end to the periodic debates on changing the project name.
>>
>> Dave Page
>> On behalf of the PostgreSQL Core Team
>>
>
> I prefer to dump all my data in a big text file and grep it for the 
> information I need.

As long as you implement your own grep, that sounds about on par with
the current trends! Go for it!


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

-- 
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] "1-Click" installer problems

[Dave Page]

On Thu, Apr 1, 2010 at 10:35 AM, Nikhil G. Daddikar  wrote:
> It is not the count that matters. It is the quality. Even after giving logs,
> your folks are unable to figure out the problem.

No, because clearly it is not a simple issue.

> I am facing the same
> problem that was faced by a lot of users on the forum and not one was
> resolved successfully.

Actually, not you weren't. Of the cases you quoted, some were from
completely different pieces of software, one was a request for
information which a sales guy followed up, and the remaining ones
included some people for whom a fix was found, some for whom one
wasn't, and some who didn't provide the required information to
diagnose the problem.

Also, there are a number of different issues that can cause an initdb
failure. We've worked hard to work around as many of those as possible
in the installer, but some are just damn hard to track down - and
pretty much impossible if people won't help us when they see a
failure. Just because you see the same outward symptoms, it does not
mean that you have the same underlying problem.

> Otherwise I would've moved ahead on my own. I am
> interested in fixing the installer. I am not interested in manual steps to
> get it working on my PC because there are a hundred other PCs that I have to
> worry about and a fix in the installer would be right step ahead. But
> someone has to admit that there is a problem in the installer.

Noone has said there isn't some situation that the installer cannot
properly handle - I'm sure there are more than a few, knowing how
widely installations of Windows can vary from machine to machine, or
domain to domain.

We want to fix any issues in the installer - but we cannot simply
guess what's going wrong. That can take time and effort to understand.

> The reason I posted this in the general newsgroup is because I thought
> others would like to know what's going on. But I think this is a newsgroup
> with a bunch of inflated egos who want to do everything else rather than
> address the problem. A typical open-source group. Evangelizing PGSQL was a
> mistake.

You've had a people try to help you on the forum.

You've had people here try to suggest how you might help us to help you.

You've even had someone someone telephone your office to try to help
(again, for free), who you refused to talk to.

Unless you are prepared to help us understand exactly what is unique
about your systems so we can figure out what is going wrong, then we
cannot help you.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

-- 
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] "1-Click" installer problems

[Sachin Srivastava]

If you really want to help and make the installer a better experience, i 
asked for helping me out debug the issue
"Run the initcluster.vbs and let us know at what line number you are 
getting the 'Object Required' error message".


But all you replied was : - " I need step by step procedure to install 
it on Vista or Windows 7, I dont want bits and pieces stuff"..


And then you expect us to help out. If you really care then help out, 
there is just no point saying, that was working and that's not again and 
again.




If you mean the number of responses, yes it's great.

The reason I posted this in the general newsgroup is because I thought 
others would like to know what's going on. But I think this is a 
newsgroup with a bunch of inflated egos who want to do everything else 
rather than address the problem. A typical open-source group. 
Evangelizing PGSQL was a mistake.





Thanks for all your help and good luck to everyone!
-n.




--
Regards,
Sachin Srivastava
EnterpriseDB , the Enterprise Postgres 
 company.

Re: [GENERAL] "1-Click" installer problems

[Nikhil G. Daddikar]

Maybe I'm missing something, but I see 5 responses from 2 of our
engineers over the last 2 days on that thread, all asking valid
questions and trying to figure out why this is failing for you, when
it does not for the vast majority of other users. I appreciate that
you do not want to do a manual installation, but for us to understand
why it is failing in your particular case, we need to do some
exploration.
   


It is not the count that matters. It is the quality. Even after giving 
logs, your folks are unable to figure out the problem. I am facing the 
same problem that was faced by a lot of users on the forum and not one 
was resolved successfully. Otherwise I would've moved ahead on my own. I 
am interested in fixing the installer. I am not interested in manual 
steps to get it working on my PC because there are a hundred other PCs 
that I have to worry about and a fix in the installer would be right 
step ahead. But someone has to admit that there is a problem in the 
installer. All arguments in this thread are junk because 8.3 installer 
for win32 from postgresql.org WORKS using the same conditions on 
Vista/2003/7 everywhere. It is nothing to do with APPDATA or any such 
thing. Something that worked was broken, it's that simple. And nobody 
wants to know what was.



Sachin told you how to install in a nutshell, but I guess you missed
that so here are the detail walkthrough instructions:
http://www.enterprisedb.com/learning/pginst_guide.do

   
I don't think that's bad for *free* support.
   


If you mean the number of responses, yes it's great.

The reason I posted this in the general newsgroup is because I thought 
others would like to know what's going on. But I think this is a 
newsgroup with a bunch of inflated egos who want to do everything else 
rather than address the problem. A typical open-source group. 
Evangelizing PGSQL was a mistake.


Thanks for all your help and good luck to everyone!
-n.

--
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] Prevent users to drop triggers applied on table

[Alban Hertroys]

On 1 Apr 2010, at 9:52, dipti shah wrote:

> Yah...that's correct but I am wondering if someone out there knows work 
> around to this.
> 
> I could make my table owner to postgres user but then is there any way 
> original user can drop the table. I want original users should allow to drop 
> their table.

If you want to give the user DROP TABLE rights, he will also need to be able to 
drop the triggers and such on that table.

If you want to work around that you'll need to take the same approach with 
which you created those tables.

Having the tables owned by a more powerful user will help with that, but in 
your situation I think the postgres superuser may be a bit too powerful. You're 
probably better off creating a non-superuser role for this purpose.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4bb4688c10419781048118!



-- 
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] prevent connection using pgpass.conf

[John R Pierce]

Christophe Dore wrote:

Thanks for answering

Yes, you are right. This is a client-side file. However, our concern is
that we have to consider this practice as a security issue. We'd like to
ban this practice for our product which is, thus, wrapping PostgresQL
engine. Thus my questions

- is there any configuration that can be done on server side to prevent
the client side to use such file to read passwords ?
- is there any options that can be set in postgres libpq C library to
prevent the connection functions to search for password in files ?
  



where do you want the client apps to get the passwords from?  hard 
coded? an application read .inf file? 




--
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] prevent connection using pgpass.conf

[Guillaume Lelarge]

Le 01/04/2010 11:21, Christophe Dore a écrit :
> Thanks for answering
> 
> Yes, you are right. This is a client-side file. However, our concern is
> that we have to consider this practice as a security issue. We'd like to
> ban this practice for our product which is, thus, wrapping PostgresQL
> engine. Thus my questions
> 
> - is there any configuration that can be done on server side to prevent
> the client side to use such file to read passwords ?

No.

> - is there any options that can be set in postgres libpq C library to
> prevent the connection functions to search for password in files ?

Well, you need to change the source code and recompile libpq. But if
your user is "smart" enough to install the "right" libpq, they will be
able to use the pgpass file.


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

-- 
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] prevent connection using pgpass.conf

[Christophe Dore]

Thanks for answering

Yes, you are right. This is a client-side file. However, our concern is
that we have to consider this practice as a security issue. We'd like to
ban this practice for our product which is, thus, wrapping PostgresQL
engine. Thus my questions

- is there any configuration that can be done on server side to prevent
the client side to use such file to read passwords ?
- is there any options that can be set in postgres libpq C library to
prevent the connection functions to search for password in files ?

Thanks

--


-Original Message-
From: Raymond O'Donnell [mailto:r...@iol.ie] 
Sent: mercredi 31 mars 2010 19:00
To: Christophe Dore
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] prevent connection using pgpass.conf

On 31/03/2010 16:32, Christophe Dore wrote:
> Hi
> 
> We are building a solution using some dedicated postgresql servers
(and
> dedicated C++ and Java apps). For security reasons, we'd like to
prevent
> users to connect (from our apps at least) to those servers with
> passwords stored in files such as pgpass.conf.

Unless I'm mistaken, my understanding is that pgpass files are stored on
client machines, not the server, so if the clients are connecting from
different machines this shouldn't be a problem in the first place.

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
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] "1-Click" installer problems

[Dave Page]

On Thu, Apr 1, 2010 at 8:24 AM, Craig Ringer
 wrote:
> Thomas Kellerer wrote:
>
>> All the posts seem to share the same root cause: the data directory has
>> been put into "c:\Program Files" but a regular user does not have write
>> permissions on that directory.
>
> Yep. They're also often confused by various steps people have tried
> while attempting to get it working, making it very hard to trace what
> actually happened initially and why.

Actually, most errors used to happen when the user wasn't using that
directory. We finally tracked down what we believe to be the primary
outstanding cause of initdb failures and fixed it for 8.4.3/8.3.10 btw
- it took a brainstorming session and a great deal of thought from
Magnus and I, as well as the help of a user from a US .gov site with
very tight security procedures to track it down.

>> I do not like the installer's suggestion to put the data directory into
>> c:\Program Files either, I think this should default to %APPDATA%

The reasons why we do that have been discussed here before - check the archives.

As a point of reference - wanna guess where Microsoft SQL Server puts
it's data by default?

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

-- 
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] [HACKERS] Postgres 9.1 - Release Theme

[Ilya Kosmodemiansky]

Nice to hear and thumbs up! I've just start planning to migrate one of
my telco 3Tb database running blunt oracle to  coachDb but now of
course postgres looks better. Hopefully stupid transactions will be
abrogated to

wbr Ilya

On Thu, Apr 1, 2010 at 12:33 PM, Dave Page  wrote:
> On Thu, Apr 1, 2010 at 9:30 AM, Thom Brown  wrote:
>> I prefer to dump all my data in a big text file and grep it for the
>> information I need.
>
> There's no need to start showing off and get all technical y'know.
>
> --
> Dave Page
> EnterpriseDB UK: http://www.enterprisedb.com
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hack...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re : Re : Re : Re : [GENERAL] Select in temporary table

[Sylvain Lara]

2010/4/1 Sylvain Lara :
>> Hello,
>>
>> I found some subjects like mine on many forums, but the given solution
>> doesn't work for me.
>>
>> I'm using PostgreSQL 8.4. I'm working on a C# application.
>>
>> When connecting to the application, a temporary table myTableTemp is
>> created.
>> This table is available for all the application duration, and is deleted
>> when the session is killed, when user closes the application.
>>
>> The use should do this, after connnecting to my application :
>>
>> - Menu item 1 : A first PL/PGSQL function inserts data in this table (and
>> temporary table already exist because created when launching application)
>> - Menu item 2 : Another PL/PGSQL function selects number of rows in this
>> table and displays it
>>
>> As I encountered my problem, I have just tried the second function (called
>> by menu item 2), that should return me 0, because the temporary table has
>> not be filled.
>>
>> When launching a first time the menu item 2, the function returns 0 ==>
>> good result
>> When launching a second time the menu item 2, I've got the following error
>> ==> table myTableTemp does not exist.
>>
>> Sometimes, the menu item 2 works many times, if I'm waiting a little time
>> between two executions, but at the end, the same error occurs.
>>
>>
>> I found on some posts solutions talking about using the EXECUTE function,
>> which I tried, but the same result still occurs.
>>
>>
>> My code is (not EXACTLY my code because just written by memory) :
>>
>> CREATE OR REPLACE FUNCTION test RETURN integer AS
>> DECLARE
>> nbLinesinteger;
>>
>> BEGIN
>>
>> EXECUTE 'select count(*) from myTableTemp' INTO nbLines;
>>
>> RETURN nbLines;
>>
>> END;
>>
>>
>> First execution :
>> select test() ==> 0
>>
>> Second execution :
>> select test() ==> table myTableTemp does not exist.
>>
>> Any ideas ?
>
> I assume you're using CREATE TEMP TABLE to make the table.  Internally,
> PostgreSQL automatically drops the table when the session is disconnected.
> Also, the temp table is not visible from any other connection.
>
> As a result, my guess is that either:
> a) Your application disconnects and reconnects between the two runs
> b) Your application establishes multiple connections at some point and
>   uses a different one on the second run
>
> It may be deep in the underlying libraries that this is happening.  I have
> seen examples of code that establishe dozens of database connections for
> a single application, because the code is poorly organized (as an example).
>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>
> ---
>
> Hello,
>
> Thank you for answering me.
> You're right, my temporary table is done like that :  CREATE TEMP TABLE
>
> My application doesn't disconnect, between two executions because sometimes,
> it works two, three, four, .. times and I've got the error on the next
> execution.
>
> I have multiple connections that's right, but these connections are in the
> same session.
> If I insert data in my table, juste after creating it (when launching my
> application), the good number of rows is displayed.
> Then sometimes, the same number is displayed on the second execution. etc...
>
> I haven't developed the core, so I don't know exactly how it works.
> But I'm sure there is no disconnection between these two executions.
>
> Thank you very much
>
> 
>
> Hello,
>
> Another test has been done, the first thing I'm doing in my PL/PGSQL
> function is :
>
> IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'myTableTemp') THEN
> raise notice 'function exists...';
> ELSE
> raise notice 'function not exists...';
> END IF;
>
>
> So my function is like that :
>
> CREATE OR REPLACE FUNCTION test()  RETURNS integer AS
> $BODY$
> DECLARE
>
> nbLinesinteger;
>
> BEGIN
>
> IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'myTableTemp')
> THEN
> raise notice 'function exists...';
> ELSE
> raise notice 'function not exists...';
> END IF;
>
> -- Select from myTableTemp
> SELECT COUNT(distinct pat_id) INTO nbLines FROM instance_fiche WHERE
> inst_id IN (select inst_id from myTableTemp);
>
>
> RETURN nbLines;
>
> EXCEPTION
> WHEN OTHERS THEN
> BEGIN
> raise exception 'Erreur function test() : %', SQLERRM;
> END;
> END;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;
>
>
> My function notices everytime 'function exists', even when I've got the
> error "relation myTableTemp does not exist".
>
> Any ideas ? I'm a little bit disappointed with this problem...
>
> Thanks a lot
>
> 
>
> My temporary table is created like that :
>
> CREATE TEMPORARY TABLE myTableTemp (inst_id integer);
>

--

De : jose javier parra sanchez 
Cc : pgsql-general@postgresql.org
Envoyé le : Jeu 1 avril

Re: [GENERAL] [HACKERS] Postgres 9.1 - Release Theme

[gabriele.bartolini]

Ciao Dave,

> You may also notice that without SQL, the project name is somewhat
> misleading. To address that, the project name will be changed to
> 'PostgreQUEL' with the 9.1 release. We expect this will also put an
> end to the periodic debates on changing the project name.

Ahahahah ... nice fish! ;)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres 9.1 - Release Theme

[Gerd Koenig]

Hi Dave,

thanks for this funny post on April, 1st ;-)

It's currently the highlight of my working day here

regards..GERD..

On Thursday 01 April 2010 10:13:16 am Dave Page wrote:
> Following a great deal of discussion, I'm pleased to announce that the
> PostgreSQL Core team has decided that the major theme for the 9.1
> release, due in 2011, will be 'NoSQL'.
> 
> There is a growing trend towards NoSQL databases, with major sites
> like Twitter and Facebook utilising them extensively. NoSQL databases
> often include multi-master replication, clustering and failover
> features that have long been requested in PostgresSQL, but have been
> extremely difficult to implement with SQL which has prevented us from
> advancing Postgree in the way that we'd like.
> 
> To address this, the intention is to remove SQL support from
> Postgres, and replace it with a language called 'QUEL'. This will
> provide us with the flexibility we need to implement the features of
> modern NoSQL databases. With no SQL support there will obviously be
> some differences in the query syntax that must be used to access your
> data. For example, the query:
> 
> select (e.salary/ (e.age - 18)) as comp from employee as e where
> e.name = "Jones"
> 
> would be rewritten as:
> 
> range of e is employee retrieve (comp = e.salary/ (e.age - 18)) where
> e.name = "Jones"
> 
> Aggregate syntax in QUEL is particularly powerful. For example, the query:
> 
> select dept,
>   avg(salary) as avg_salary,
>   sum(salary) as tot_salary
> from
>   employees
> group by
>   dept
> 
> may be written as:
> 
> range of e is employee
> retrieve (e.dept,
>  avg_salary = avg(e.salary by e.dept),
>  tot_salary = sum(e.salary by e.dept)
> )
> 
> Note that the grouped column can be specified for each individual
> aggregate.
> 
> We will be producing a comprehensive guide to the QUEL syntax to aid
> with application migration. We appreciate the difficulty that this
> change may cause some users, but feel we must embrace the NoSQL
> philosophy in order to remain "The world's most advanced Open Source
> database"
> 
> "There's no question that, at 21 years old, the SQL standard is past its
> prime," said core developer and standards expert Peter Eisentraut. "It's
> time for us to switch to something fresher.  I personally would have
> preferred XSLT, but QUEL is almost as good."
> 
> Project committer Heikki Linnakangas added: "By replacing SQL with
> QUEL not only will will be able to add new features to Postgres that
> were previously too difficult, but we'll also increase user loyalty as
> it'll be much harder for them to change to a different, SQL-based
> database. That'll be pretty cool."
> 
> You may also notice that without SQL, the project name is somewhat
> misleading. To address that, the project name will be changed to
> 'PostgreQUEL' with the 9.1 release. We expect this will also put an
> end to the periodic debates on changing the project name.
> 
> Dave Page
> On behalf of the PostgreSQL Core Team

-- 
/\
| Gerd König
| - Service Manager IT -
|
| TRANSPOREON GmbH
| Magirus-Deutz-Str. 16 
| DE - 89077 Ulm
|
| Tel: +49 [0]731 16906 106
| Fax: +49 [0]731 16906 99
| koe...@transporeon.com
| www.transporeon.com
|
\/


TTRANSPOREON GmbH, District Court Ulm, HRB 722056,
Directors: Peter Förster, Roland Hötzl, Martin Mack, Marc-Oliver Simon

-- 
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] [HACKERS] Postgres 9.1 - Release Theme

[Dave Page]

On Thu, Apr 1, 2010 at 9:30 AM, Thom Brown  wrote:
> I prefer to dump all my data in a big text file and grep it for the
> information I need.

There's no need to start showing off and get all technical y'know.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

-- 
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] [HACKERS] Postgres 9.1 - Release Theme

[Thom Brown]

On 1 April 2010 09:13, Dave Page  wrote:

> Following a great deal of discussion, I'm pleased to announce that the
> PostgreSQL Core team has decided that the major theme for the 9.1
> release, due in 2011, will be 'NoSQL'.
>
> There is a growing trend towards NoSQL databases, with major sites
> like Twitter and Facebook utilising them extensively. NoSQL databases
> often include multi-master replication, clustering and failover
> features that have long been requested in PostgresSQL, but have been
> extremely difficult to implement with SQL which has prevented us from
> advancing Postgree in the way that we'd like.
>
> To address this, the intention is to remove SQL support from
> Postgres, and replace it with a language called 'QUEL'. This will
> provide us with the flexibility we need to implement the features of
> modern NoSQL databases. With no SQL support there will obviously be
> some differences in the query syntax that must be used to access your
> data. For example, the query:
>
> select (e.salary/ (e.age - 18)) as comp from employee as e where
> e.name = "Jones"
>
> would be rewritten as:
>
> range of e is employee retrieve (comp = e.salary/ (e.age - 18)) where
> e.name = "Jones"
>
> Aggregate syntax in QUEL is particularly powerful. For example, the query:
>
> select dept,
>  avg(salary) as avg_salary,
>  sum(salary) as tot_salary
> from
>  employees
> group by
>  dept
>
> may be written as:
>
> range of e is employee
> retrieve (e.dept,
> avg_salary = avg(e.salary by e.dept),
> tot_salary = sum(e.salary by e.dept)
> )
>
> Note that the grouped column can be specified for each individual
> aggregate.
>
> We will be producing a comprehensive guide to the QUEL syntax to aid
> with application migration. We appreciate the difficulty that this
> change may cause some users, but feel we must embrace the NoSQL
> philosophy in order to remain "The world's most advanced Open Source
> database"
>
> "There's no question that, at 21 years old, the SQL standard is past its
> prime," said core developer and standards expert Peter Eisentraut. "It's
> time for us to switch to something fresher.  I personally would have
> preferred XSLT, but QUEL is almost as good."
>
> Project committer Heikki Linnakangas added: "By replacing SQL with
> QUEL not only will will be able to add new features to Postgres that
> were previously too difficult, but we'll also increase user loyalty as
> it'll
> be much harder for them to change to a different, SQL-based
> database. That'll be pretty cool."
>
> You may also notice that without SQL, the project name is somewhat
> misleading. To address that, the project name will be changed to
> 'PostgreQUEL' with the 9.1 release. We expect this will also put an
> end to the periodic debates on changing the project name.
>
> Dave Page
> On behalf of the PostgreSQL Core Team
>
>
I prefer to dump all my data in a big text file and grep it for the
information I need.

Thom

Re: [GENERAL] "1-Click" installer problems

[Dave Page]

On Thu, Apr 1, 2010 at 6:47 AM, Nikhil G. Daddikar  wrote:
> Folks,
>
> Here is my original ticket. It has the screen shot  as well as the logs.
> Like many other threads on this forum it remains unresolved.
>
> http://forums.enterprisedb.com/posts/list/2235.page
>
> What I would like from EDB is a list of instructions on how to install it on
> Vista or Windows 7. I've asked this in the forum but I have not received any
> response. Is this too much to ask?

Maybe I'm missing something, but I see 5 responses from 2 of our
engineers over the last 2 days on that thread, all asking valid
questions and trying to figure out why this is failing for you, when
it does not for the vast majority of other users. I appreciate that
you do not want to do a manual installation, but for us to understand
why it is failing in your particular case, we need to do some
exploration.

Sachin told you how to install in a nutshell, but I guess you missed
that so here are the detail walkthrough instructions:
http://www.enterprisedb.com/learning/pginst_guide.do

I don't think that's bad for *free* support.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

-- 
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 9.1 - Release Theme

[Dave Page]

Following a great deal of discussion, I'm pleased to announce that the
PostgreSQL Core team has decided that the major theme for the 9.1
release, due in 2011, will be 'NoSQL'.

There is a growing trend towards NoSQL databases, with major sites
like Twitter and Facebook utilising them extensively. NoSQL databases
often include multi-master replication, clustering and failover
features that have long been requested in PostgresSQL, but have been
extremely difficult to implement with SQL which has prevented us from
advancing Postgree in the way that we'd like.

To address this, the intention is to remove SQL support from
Postgres, and replace it with a language called 'QUEL'. This will
provide us with the flexibility we need to implement the features of
modern NoSQL databases. With no SQL support there will obviously be
some differences in the query syntax that must be used to access your
data. For example, the query:

select (e.salary/ (e.age - 18)) as comp from employee as e where
e.name = "Jones"

would be rewritten as:

range of e is employee retrieve (comp = e.salary/ (e.age - 18)) where
e.name = "Jones"

Aggregate syntax in QUEL is particularly powerful. For example, the query:

select dept,
  avg(salary) as avg_salary,
  sum(salary) as tot_salary
from
  employees
group by
  dept

may be written as:

range of e is employee
retrieve (e.dept,
 avg_salary = avg(e.salary by e.dept),
 tot_salary = sum(e.salary by e.dept)
)

Note that the grouped column can be specified for each individual
aggregate.

We will be producing a comprehensive guide to the QUEL syntax to aid
with application migration. We appreciate the difficulty that this
change may cause some users, but feel we must embrace the NoSQL
philosophy in order to remain "The world's most advanced Open Source
database"

"There's no question that, at 21 years old, the SQL standard is past its
prime," said core developer and standards expert Peter Eisentraut. "It's
time for us to switch to something fresher.  I personally would have
preferred XSLT, but QUEL is almost as good."

Project committer Heikki Linnakangas added: "By replacing SQL with
QUEL not only will will be able to add new features to Postgres that
were previously too difficult, but we'll also increase user loyalty as it'll
be much harder for them to change to a different, SQL-based
database. That'll be pretty cool."

You may also notice that without SQL, the project name is somewhat
misleading. To address that, the project name will be changed to
'PostgreQUEL' with the 9.1 release. We expect this will also put an
end to the periodic debates on changing the project name.

Dave Page
On behalf of the PostgreSQL Core Team

-- 
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] "1-Click" installer problems

[Dave Page]

On Thu, Apr 1, 2010 at 8:57 AM, John R Pierce  wrote:
> I too much preferred the older installer to that oneclick thing, and regret
> that we the users aren't at least given a choice.

You're free to maintain the old one if you like. Be warned though - it
had a much higher failure rate than the one-click installer (which
based on the ratio of downloads to bug reports, is down to something
like 45000:1 at this point, or a 99.997% success rate).


-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

-- 
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] "1-Click" installer problems

[John R Pierce]

I too much preferred the older installer to that oneclick thing, and 
regret that we the users aren't at least given a choice.




--
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] Prevent users to drop triggers applied on table

[dipti shah]

Yah...that's correct but I am wondering if someone out there knows work
around to this.

I could make my table owner to postgres user but then is there any way
original user can drop the table. I want original users should allow to drop
their table.

Thanks,
Dipti

On Thu, Apr 1, 2010 at 12:58 PM, John R Pierce  wrote:

> dipti shah wrote:
>
>> Hi,
>>
>> We have been using a stored procedure which allow users to create table.
>> The stored procedure creates actual table, logging tables, views, and
>> applies triggers. I want to prevent table owner from deleting the triggers
>> applied on table by create table stored procedure. Could anyone please
>> suggest me to get way out here.
>>
>
> if you don't want someone to modify tables, including dropping triggers,
> then they shouldn't be the owner of it.  the owner has full rights to a
> table, including the right to delete it, alter it, etc.
>
>
>
> --
> 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] "1-Click" installer problems

[Thomas Kellerer]

Craig Ringer, 01.04.2010 09:24:

I do not like the installer's suggestion to put the data directory into
c:\Program Files either, I think this should default to %APPDATA%


That seems fairly sensible *IF* it checks very carefully to make sure
the postgresql user does not have a roaming profile, ie they're a local
user not a domain user.


I think the installer should simply not "suggest" any directory, but force the 
user to select one manually (maybe even activley prevent c:\Program Files). I don't know 
if this is possible (or how hard it would be) but I think a very useful feature for the 
installer would be to try to check the permissions that the service account has on the 
chosen data directory.


If the datadir was put in an account with roaming profiles enabled,
Windows would try to sync the datadir to and from the profile share on
the server at every user login/logout.


Ah, didn't think of that one.
 

such as a virus scanner or some funky option.


True.
In the german Postgres forum there are several posts regarding that topic.

It seems that especially Norton and the Windows built-in Antivirus do not work 
well with Postgres.
Personally I have no problems with Sophos and Avira

Thomas




--
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] Prevent users to drop triggers applied on table

[John R Pierce]

dipti shah wrote:

Hi,

We have been using a stored procedure which allow users to create 
table. The stored procedure creates actual table, logging tables, 
views, and applies triggers. I want to prevent table owner from 
deleting the triggers applied on table by create table stored 
procedure. Could anyone please suggest me to get way out here.


if you don't want someone to modify tables, including dropping triggers, 
then they shouldn't be the owner of it.  the owner has full rights to a 
table, including the right to delete it, alter it, etc.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Re : Re : Re : [GENERAL] Select in temporary table

[jose javier parra sanchez]

Not sure about how de C# driver you use to connect, but if you have
multiple connections, then you have multiple sessions.

2010/4/1 Sylvain Lara :
>> Hello,
>>
>> I found some subjects like mine on many forums, but the given solution
>> doesn't work for me.
>>
>> I'm using PostgreSQL 8.4. I'm working on a C# application.
>>
>> When connecting to the application, a temporary table myTableTemp is
>> created.
>> This table is available for all the application duration, and is deleted
>> when the session is killed, when user closes the application.
>>
>> The use should do this, after connnecting to my application :
>>
>> - Menu item 1 : A first PL/PGSQL function inserts data in this table (and
>> temporary table already exist because created when launching application)
>> - Menu item 2 : Another PL/PGSQL function selects number of rows in this
>> table and displays it
>>
>> As I encountered my problem, I have just tried the second function (called
>> by menu item 2), that should return me 0, because the temporary table has
>> not be filled.
>>
>> When launching a first time the menu item 2, the function returns 0 ==>
>> good result
>> When launching a second time the menu item 2, I've got the following error
>> ==> table myTableTemp does not exist.
>>
>> Sometimes, the menu item 2 works many times, if I'm waiting a little time
>> between two executions, but at the end, the same error occurs.
>>
>>
>> I found on some posts solutions talking about using the EXECUTE function,
>> which I tried, but the same result still occurs.
>>
>>
>> My code is (not EXACTLY my code because just written by memory) :
>>
>> CREATE OR REPLACE FUNCTION test RETURN integer AS
>> DECLARE
>>         nbLines    integer;
>>
>> BEGIN
>>
>>         EXECUTE 'select count(*) from myTableTemp' INTO nbLines;
>>
>>         RETURN nbLines;
>>
>> END;
>>
>>
>> First execution :
>> select test() ==> 0
>>
>> Second execution :
>> select test() ==> table myTableTemp does not exist.
>>
>> Any ideas ?
>
> I assume you're using CREATE TEMP TABLE to make the table.  Internally,
> PostgreSQL automatically drops the table when the session is disconnected.
> Also, the temp table is not visible from any other connection.
>
> As a result, my guess is that either:
> a) Your application disconnects and reconnects between the two runs
> b) Your application establishes multiple connections at some point and
>   uses a different one on the second run
>
> It may be deep in the underlying libraries that this is happening.  I have
> seen examples of code that establishe dozens of database connections for
> a single application, because the code is poorly organized (as an example).
>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>
> ---
>
> Hello,
>
> Thank you for answering me.
> You're right, my temporary table is done like that :  CREATE TEMP TABLE
>
> My application doesn't disconnect, between two executions because sometimes,
> it works two, three, four, .. times and I've got the error on the next
> execution.
>
> I have multiple connections that's right, but these connections are in the
> same session.
> If I insert data in my table, juste after creating it (when launching my
> application), the good number of rows is displayed.
> Then sometimes, the same number is displayed on the second execution. etc...
>
> I haven't developed the core, so I don't know exactly how it works.
> But I'm sure there is no disconnection between these two executions.
>
> Thank you very much
>
> 
>
> Hello,
>
> Another test has been done, the first thing I'm doing in my PL/PGSQL
> function is :
>
> IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'myTableTemp') THEN
>         raise notice 'function exists...';
> ELSE
>         raise notice 'function not exists...';
> END IF;
>
>
> So my function is like that :
>
> CREATE OR REPLACE FUNCTION test()  RETURNS integer AS
> $BODY$
> DECLARE
>
>     nbLines    integer;
>
> BEGIN
>
>         IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'myTableTemp')
> THEN
>                 raise notice 'function exists...';
>         ELSE
>                 raise notice 'function not exists...';
>         END IF;
>
>         -- Select from myTableTemp
>         SELECT COUNT(distinct pat_id) INTO nbLines FROM instance_fiche WHERE
> inst_id IN (select inst_id from myTableTemp);
>
>
>     RETURN nbLines;
>
> EXCEPTION
>     WHEN OTHERS THEN
>         BEGIN
>             raise exception 'Erreur function test() : %', SQLERRM;
>         END;
> END;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;
>
>
> My function notices everytime 'function exists', even when I've got the
> error "relation myTableTemp does not exist".
>
> Any ideas ? I'm a little bit disappointed with this problem...
>
> Thanks a lot
>
> 
>
> My temporary table is created like that :
>
> CREATE TEMPORARY TABLE myTableTemp (inst_id integer);
>
>
>
>

-- 
Sent

Re: [GENERAL] "1-Click" installer problems

[Craig Ringer]

Thomas Kellerer wrote:

> All the posts seem to share the same root cause: the data directory has
> been put into "c:\Program Files" but a regular user does not have write
> permissions on that directory.

Yep. They're also often confused by various steps people have tried
while attempting to get it working, making it very hard to trace what
actually happened initially and why.

> I do not like the installer's suggestion to put the data directory into
> c:\Program Files either, I think this should default to %APPDATA%

That seems fairly sensible *IF* it checks very carefully to make sure
the postgresql user does not have a roaming profile, ie they're a local
user not a domain user.

If the datadir was put in an account with roaming profiles enabled,
Windows would try to sync the datadir to and from the profile share on
the server at every user login/logout.

This could easily happen if the user overrode the usual account setup
and told the installer to just use their own account. So it needs to be
checked for and detected if %APPDATA% is to be used.

That said, C:\Users\postgresql\AppData\Local\Postgresql is indeed a very
good place to store the database.

> instead of %ProgramFile%. I bet half of the problems would go away if
> the installer refused to put the data directory into c:\Program Files.

Yep - it's not a clever place to put it.

> Given the fact that Microsoft finally tries to enforce people not to
> work as Administrators makes this even more important.

They also very strongly discourage storage of variable data in Program
Files. It's like Pg putting it's database in /usr/pgdata .

That said, on my Vista box (which has UAC fully enabled and on which Pg
runs as a normal user account) I had no problems with a default install
with datadir in program files. There's some other factor involved
causing this failure, such as a virus scanner or some funky option.

--
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

Re: [GENERAL] "1-Click" installer problems

[Nikhil G. Daddikar]

On 01-04-2010 12:39, Thomas Kellerer wrote:

Nikhil G. Daddikar, 01.04.2010 08:04:


In about 30 seconds I found the following unanswered threads relating to
installation on Windows Vista. If anybody is interested I can find more.


The problem with this kind of statistics is that you will only find 
people who complain, you'll never find people who do not complain 
because they have no problems. Actually that's true for all internet 
forums or mailing lists: you'll seldomly find people posting something 
like  "Hey everything works fine, I had no problems".


I agree... but they are still unanswered. And what else can I do. I am 
facing problems on multiple computers, my customers are facing problems 
as well. NOBODY till date has managed to install 8.4 on Vista using the 
1-click installer and EVERYBODY has managed to install the 8.3 installer 
(from postgresql.org) on Vista. DOES THIS COUNT AS A VALID STATISTIC?  
And yes, i have tried installing it in C:\Postgresql as well. 
Interesting to note that 8.3 installer from postgresql.org installs 
perfectly in 'C:\Program Files' even on Vista. No use blaming Windows 
all the time. It is the installer that is buggy.





All the posts seem to share the same root cause: the data directory 
has been put into "c:\Program Files" but a regular user does not have 
write permissions on that directory. As the installer is usually run 
with Administrator rights, the directory can be created but the 
service (or initdb) runs under a normal user account that cannot write 
to that directory because.


I do not like the installer's suggestion to put the data directory 
into c:\Program Files either, I think this should default to %APPDATA% 
instead of %ProgramFile%. I bet half of the problems would go away if 
the installer refused to put the data directory into c:\Program Files.


Given the fact that Microsoft finally tries to enforce people not to 
work as Administrators makes this even more important.


My suggestion is to try to use a different data directory when 
installing Postgres and make sure that the postgres service account is 
allowed to read and write that directory.


Personally I switched to using the ZIP packages completely because it 
is so much easer (unzip, initdb, pg_ctl -register, done)


Thomas







--
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] Get the list of permissions on schema for current user

[Jignesh Shah]

I don't think you can do it. You have to parse the string you got from
pg_namespace to get the current user's permissions.


On Thu, Apr 1, 2010 at 11:09 AM, dipti shah  wrote:

> Hi,
>
> I ran below command to list out all privileges of objects if "mydb" schema.
> Actually, I want to know what are the permissions "user1" has on mydb
> schema. Could you please tell me how to do this?
>
> mydb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn where
> pc.relnamespace=pn.oid and pn.nspname='mydb';
>  relname  |relacl
>
> --+---
>   mylog   |
> {postgres=arwdDxt/postgres,=arwdDxt/postgres}
>   techtable   |
> {postgres=arwdDxt/postgres,=ar/postgres}
>   techtable_log   |
>   hrtable |
> {postgres=arwdDxt/postgres,=ar/postgres}
>   hrtable_log |
> (5 rows)
>
>
> mydb=> select current_user;
>  current_user
> --
>  user1
> (1 row)
>
> mydb=>
>
> Thanks, Dipti
>
>

Re : Re : Re : [GENERAL] Select in temporary table

[Sylvain Lara]

> Hello,
> 
> I found some subjects like mine on many forums, but the given solution 
> doesn't work for me.
> 
> I'm using PostgreSQL 8.4. I'm working on a C# application.
> 
> When connecting to the application, a temporary table myTableTemp is created.
> This table is available for all the application duration, and is deleted when 
> the session is killed, when user closes the application.
> 
> The use should do this, after connnecting to my application :
> 
> - Menu item 1 : A first PL/PGSQL function inserts data in this table (and 
> temporary table already exist because created when launching application)
> - Menu item 2 : Another PL/PGSQL function selects number of rows in this 
> table and displays it
> 
> As I encountered my problem, I have just tried the second function (called by 
> menu item 2), that should return me 0, because the temporary table has not be 
> filled.
> 
> When launching a first time the menu item 2, the function returns 0 ==> good 
> result
> When launching a second time the menu item 2, I've got the following error 
> ==> table myTableTemp does not exist.
> 
> Sometimes, the menu item 2 works many times, if I'm waiting a little time 
> between two executions, but at the end, the same error occurs.
> 
> 
> I found on some posts solutions talking about using the EXECUTE function, 
> which I tried, but the same result still occurs.
> 
> 
> My code is (not EXACTLY my code because just written by memory) :
> 
> CREATE OR REPLACE FUNCTION test RETURN integer AS
> DECLARE
> nbLinesinteger;
> 
> BEGIN
> 
> EXECUTE 'select count(*) from myTableTemp' INTO nbLines;
> 
> RETURN nbLines;
> 
> END;
> 
> 
> First execution :
> select test() ==> 0
> 
> Second execution :
> select test() ==> table myTableTemp does not exist.
> 
> Any ideas ?

I assume you're using CREATE TEMP TABLE to make the table.  Internally,
PostgreSQL automatically drops the table when the session is disconnected.
Also, the temp table is not visible from any other connection.

As a result, my guess is that either:
a) Your application disconnects and reconnects between the two runs
b) Your application establishes multiple connections at some point and
  uses a different one on the second run

It may be deep in the underlying libraries that this is happening.  I have
seen examples of code that establishe dozens of database connections for
a single application, because the code is poorly organized (as an example).

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

 
---
 

Hello,
 
Thank you for answering me.
You're right, my temporary table is done like that :  CREATE TEMP TABLE
 
My application doesn't disconnect, between two executions because sometimes, it 
works two, three, four, .. times and I've got the error on the next execution.
 
I have multiple connections that's right, but these connections are in the same 
session.
If I insert data in my table, juste after creating it (when launching my 
application), the good number of rows is displayed.
Then sometimes, the same number is displayed on the second execution. etc...
 
I haven't developed the core, so I don't know exactly how it works.
But I'm sure there is no disconnection between these two executions.
 
Thank you very much
 

Hello,

Another test has been done, the first thing I'm doing in my PL/PGSQL function 
is :

IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'myTableTemp') THEN
raise notice 'function exists...';
ELSE
raise notice 'function not exists...';
END IF;


So my function is like that :

CREATE OR REPLACE FUNCTION test()  RETURNS integer AS
$BODY$
DECLARE

nbLinesinteger;

BEGIN

IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'myTableTemp') THEN
raise notice 'function exists...';
ELSE
raise notice 'function not exists...';
END IF;

-- Select from myTableTemp
SELECT COUNT(distinct pat_id) INTO nbLines FROM instance_fiche WHERE 
inst_id IN (select inst_id from myTableTemp);


RETURN nbLines;

EXCEPTION 
WHEN OTHERS THEN
BEGIN
raise exception 'Erreur function test() : %', SQLERRM;
END;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


My function notices everytime 'function exists', even when I've got the error 
"relation myTableTemp does not exist".

Any ideas ? I'm a little bit disappointed with this problem...

Thanks a lot



My temporary table is created like that :

CREATE TEMPORARY TABLE myTableTemp(inst_id integer); 


  

[GENERAL] Prevent users to drop triggers applied on table

[dipti shah]

Hi,

We have been using a stored procedure which allow users to create table. The
stored procedure creates actual table, logging tables, views, and applies
triggers. I want to prevent table owner from deleting the triggers applied
on table by create table stored procedure. Could anyone please suggest me to
get way out here.

Thanks,
Dipti

Re: [GENERAL] "1-Click" installer problems

[Thomas Kellerer]

Nikhil G. Daddikar, 01.04.2010 08:04:


In about 30 seconds I found the following unanswered threads relating to
installation on Windows Vista. If anybody is interested I can find more.


The problem with this kind of statistics is that you will only find people who complain, 
you'll never find people who do not complain because they have no problems. Actually 
that's true for all internet forums or mailing lists: you'll seldomly find people posting 
something like  "Hey everything works fine, I had no problems".

All the posts seem to share the same root cause: the data directory has been put into 
"c:\Program Files" but a regular user does not have write permissions on that 
directory. As the installer is usually run with Administrator rights, the directory can 
be created but the service (or initdb) runs under a normal user account that cannot write 
to that directory because.

I do not like the installer's suggestion to put the data directory into 
c:\Program Files either, I think this should default to %APPDATA% instead of 
%ProgramFile%. I bet half of the problems would go away if the installer 
refused to put the data directory into c:\Program Files.

Given the fact that Microsoft finally tries to enforce people not to work as 
Administrators makes this even more important.

My suggestion is to try to use a different data directory when installing 
Postgres and make sure that the postgres service account is allowed to read and 
write that directory.

Personally I switched to using the ZIP packages completely because it is so 
much easer (unzip, initdb, pg_ctl -register, done)

Thomas




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re : Re : [GENERAL] Select in temporary table

[Sylvain Lara]

> Hello,
> 
> I found some subjects like mine on many forums, but the given solution 
> doesn't work for me.
> 
> I'm using PostgreSQL 8.4. I'm working on a C# application.
> 
> When connecting to the application, a temporary table myTableTemp is created.
> This table is available for all the application duration, and is deleted when 
> the session is killed, when user closes the application.
> 
> The use should do this, after connnecting to my application :
> 
> - Menu item 1 : A first PL/PGSQL function inserts data in this table (and 
> temporary table already exist because created when launching application)
> - Menu item 2 : Another PL/PGSQL function selects number of rows in this 
> table and displays it
> 
> As I encountered my problem, I have just tried the second function (called by 
> menu item 2), that should return me 0, because the temporary table has not be 
> filled.
> 
> When launching a first time the menu item 2, the function returns 0 ==> good 
> result
> When launching a second time the menu item 2, I've got the following error 
> ==> table myTableTemp does not exist.
> 
> Sometimes, the menu item 2 works many times, if I'm waiting a little time 
> between two executions, but at the end, the same error occurs.
> 
> 
> I found on some posts solutions talking about using the EXECUTE function, 
> which I tried, but the same result still occurs.
> 
> 
> My code is (not EXACTLY my code because just written by memory) :
> 
> CREATE OR REPLACE FUNCTION test RETURN integer AS
> DECLARE
> nbLinesinteger;
> 
> BEGIN
> 
> EXECUTE 'select count(*) from myTableTemp' INTO nbLines;
> 
> RETURN nbLines;
> 
> END;
> 
> 
> First execution :
> select test() ==> 0
> 
> Second execution :
> select test() ==> table myTableTemp does not exist.
> 
> Any ideas ?

I assume you're using CREATE TEMP TABLE to make the table.  Internally,
PostgreSQL automatically drops the table when the session is disconnected.
Also, the temp table is not visible from any other connection.

As a result, my guess is that either:
a) Your application disconnects and reconnects between the two runs
b) Your application establishes multiple connections at some point and
  uses a different one on the second run

It may be deep in the underlying libraries that this is happening.  I have
seen examples of code that establishe dozens of database connections for
a single application, because the code is poorly organized (as an example).

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

 
---
 

Hello,
 
Thank you for answering me.
You're right, my temporary table is done like that :  CREATE TEMP TABLE
 
My application doesn't disconnect, between two executions because sometimes, it 
works two, three, four, .. times and I've got the error on the next execution.
 
I have multiple connections that's right, but these connections are in the same 
session.
If I insert data in my table, juste after creating it (when launching my 
application), the good number of rows is displayed.
Then sometimes, the same number is displayed on the second execution. etc...
 
I haven't developed the core, so I don't know exactly how it works.
But I'm sure there is no disconnection between these two executions.
 
Thank you very much
 

Hello,

Another test has been done, the first thing I'm doing in my PL/PGSQL function 
is :

IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'myTableTemp') THEN
raise notice 'function exists...';
ELSE
raise notice 'function not exists...';
END IF;


So my function is like that :

CREATE OR REPLACE FUNCTION test()  RETURNS integer AS
$BODY$
DECLARE

nbLinesinteger;

BEGIN

IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'myTableTemp') THEN
raise notice 'function exists...';
ELSE
raise notice 'function not exists...';
END IF;

-- Select from myTableTemp
SELECT COUNT(distinct pat_id) INTO nbLines FROM instance_fiche WHERE 
inst_id IN (select inst_id from myTableTemp);


RETURN nbLines;

EXCEPTION 
WHEN OTHERS THEN
BEGIN
raise exception 'Erreur function test() : %', SQLERRM;
END;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


My function notices everytime 'function exists', even when I've got the error 
"relation myTableTemp does not exist".

Any ideas ? I'm a little bit disappointed with this problem...

Thanks a lot