Hi
I come from a MSSQL background and am trying to figure out how to write
deployment scripts for PostgreSQL. Typically, if I want to drop a
function, I would write a script that first checks for it's existence and
then performs the drop.
In MSSQL it would be as easy as (this can be done in nativ
I've entries with id's like:
x | id
---+
b | 1
a | 4
e | 5
c | 12
d | 19
(5 rows)
now I'd like to have the id in continuing number to get:
x | id
---+
b | 1
a | 2
e | 3
c | 4
d | 5
(5 rows)
Simpliest way to do would be to create a sequence and update the whole
O.B. wrote:
Richard Huxton wrote:
O.B. wrote:
Here's additional information from the log file surrounding the
problem with initdb failing:
Action 18:10:09: SetPermissions. Setting filesystem permissions...
MSI (s) (2C:AC) [18:10:09:323]: Executing op:
CustomActionSchedule(Action=SetPermiss
Jason Tesser wrote:
I just upgraded 7.4 to 8.0 and I have noticed that i am getting alot
of error that are saying i have a type problem. For example it is
saying big int expected but it was sent character varying.
Is it right? Do you have an example you could give?
> These
same queries use t
Hi!
>select 1 where 'vasya' = (select 'vasya')
>produces
>ERROR: failed to find conversion function from "unknown" to text
I managed to find two similar messages
in GENERAL archive.
In first message
http://archives.postgresql.org/pgsql-general/2004-10/msg01368.php
Tom Lane says explicit cast i
[EMAIL PROTECTED] wrote:
Hi
I come from a MSSQL background and am trying to figure out how to write
deployment scripts for PostgreSQL. Typically, if I want to drop a
function, I would write a script that first checks for it's existence and
then performs the drop.
In MSSQL it would be as easy as
> [EMAIL PROTECTED] wrote:
>> Hi
>>
>> I come from a MSSQL background and am trying to figure out how to write
>> deployment scripts for PostgreSQL. Typically, if I want to drop a
>> function, I would write a script that first checks for it's existence
>> and
>> then performs the drop.
>>
>> In MSS
On Jun 21, 2005, at 5:59 PM, [EMAIL PROTECTED] wrote:
One other question: Since in PostgreSQL you can have "overloaded"
functions, how do you query the system tables for the existence of a
particular version of the function?
The pg_proc table (which contains the functions) includes a field
How about:
update table set id = (select count(*) from table t2 where t2.id <= table.id);
Ugly as hell, but it should work.
Hope this helps,
On Tue, Jun 21, 2005 at 10:06:40AM +0200, peter pilsl wrote:
>
>
> I've entries with id's like:
>
> x | id
> ---+
> b | 1
> a | 4
> e | 5
>
Hi,
my colleague unfortunately ran some database dump in template1 on our
production server, pg7.3.5. Can we drop template1, dump template0 and
rebuild template1 from this dump? Or does exist some better way how to
repair template1 without any affect of living database instances?
Thanks,
paj
Jan Poslusny wrote:
Hi,
my colleague unfortunately ran some database dump in template1 on our
production server, pg7.3.5. Can we drop template1, dump template0 and
rebuild template1 from this dump? Or does exist some better way how to
repair template1 without any affect of living database inst
Hi Richard and thank you for your help. Here is the actual message from
the pg log
ERROR: 42804: column "datetimein" is of type timestamp without time
zone but expression is of type character varying
HINT: You will need to rewrite or cast the expression.
I can aviod this by rewritting my quer
On Tue, Jun 21, 2005 at 09:16:08 +0200,
[EMAIL PROTECTED] wrote:
> I come from a MSSQL background and am trying to figure out how to write
> deployment scripts for PostgreSQL. Typically, if I want to drop a
> function, I would write a script that first checks for it's existence and
> then perfo
Jason Tesser wrote:
Hi Richard and thank you for your help. Here is the actual message from
the pg log
ERROR: 42804: column "datetimein" is of type timestamp without time
zone but expression is of type character varying
HINT: You will need to rewrite or cast the expression.
I can aviod thi
hi
i trying to compile PostgreSQL (with perl) on Solaris
and i get an error
my perl version is 5.8.5
end of the execution trace :
[...]
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels
-fno-strict-aliasing -I../../../../src/include -c -o tupdesc.o tupdesc.c
In file included f
I am trying to optimize a delete query that's currently taking 4 hours
to run. My first step was to add some indexes on some of my FK's. That
sped up my select queries by as much as ~20x. From 3.5 sec to < 0.25
sec. They didn't help my delete query. I ran an explain analyze on the
delete query
HI
>
> Are you sure the issue is the change from PG7.4=>PG8.0, or have you
> upgraded your jdbc package at the same time?
I have upgraded the driver to the version that matched pg 8.0
> > Here is my prepared statment in my java class
> >
> > private static final String MANUALINSERT =
> >
On Tue, Jun 21, 2005 at 02:42:00PM +0200, FERREIRA, William (COFRAMI) wrote:
>
> i trying to compile PostgreSQL (with perl) on Solaris
> and i get an error
What version of Solaris and what compiler and version? I compile
several versions of PostgreSQL on Solaris 9 using gcc 3.4.2 without
any pro
Martijn van Oosterhout wrote:
How about:
update table set id = (select count(*) from table t2 where t2.id <= table.id);
Ugly as hell, but it should work.
thnx a lot. But it does not work as expected cause the update-statement
ist not commiting for the whole table during the execution. So t
Jan Poslusny <[EMAIL PROTECTED]> writes:
> my colleague unfortunately ran some database dump in template1 on our
> production server, pg7.3.5. Can we drop template1, dump template0 and
> rebuild template1 from this dump? Or does exist some better way how to
> repair template1 without any affect
Jason Tesser wrote:
HI
Are you sure the issue is the change from PG7.4=>PG8.0, or have you
upgraded your jdbc package at the same time?
I have upgraded the driver to the version that matched pg 8.0
I think your previous version should work just fine.
here is where I am executing the sta
On Tue, Jun 21, 2005 at 03:23:07PM +0200, peter pilsl wrote:
> Martijn van Oosterhout wrote:
> >How about:
> >
> >update table set id = (select count(*) from table t2 where t2.id <=
> >table.id);
> >
> >Ugly as hell, but it should work.
> >
>
>
> thnx a lot. But it does not work as expected caus
"FERREIRA, William (COFRAMI)" <[EMAIL PROTECTED]> writes:
> ../../../../src/include/utils/builtins.h:837: internal compiler error:
> Segmentation Fault
> Please submit a full bug report,
> with preprocessed source if appropriate.
> See http://gcc.gnu.org/bugs.html> for instructions.
If you don't mind creating a psql function, I guess you could do something
like that
CREATE OR REPLACE FUNCTION Update_voev_content( ) RETURNS int4 AS $$
DECLARE
_record RECORD;
_rank int4;
BEGIN
_rank := 0;
FOR _record IN ( SELECT rank FROM voev_content ORDER BY rank )
LOOP
UPDATE v
Relyea, Mike wrote:
I am trying to optimize a delete query that's currently taking 4 hours
to run. My first step was to add some indexes on some of my FK's. That
sped up my select queries by as much as ~20x. From 3.5 sec to < 0.25
sec. They didn't help my delete query. I ran an explain analy
solaris 8
gcc 3.3.2
postgresql : 8.0.3
thanks
-Message d'origine-
De : [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] la part de Michael Fuhr
Envoyé : mardi 21 juin 2005 15:21
À : FERREIRA, William (COFRAMI)
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] compilation postgresql/solar
On Jun 20, 2005, at 10:28 PM, Andrew L. Gould wrote:
compressed database backups is greater than 1GB; and the results of a
gzipped pg_dumpall is approximately 3.5GB. The processes for creating
the iso image and burning the image to DVD-R finish without any
problems; but the resulting file is u
I did install successully postgres 8.03 but I am getting the following
error message in the syslog eventhough /var/pgsql/data is pointing to new
configuration file which is postgres 8.03.
"ksqo" is configuration parameter for the postgres 7.4
Please advice me to fix this problem.
Jun 20 18:38:
Richard Huxton writes:
> Relyea, Mike wrote:
>> Any suggestions for how to get the explain analyze output?
> You should get *some* output. Unfortunately, I don't think it will show
> you anything useful. The effort is almost certainly all going on the
> FK's and you can't see through the trigge
"Prasad Duggineni" <[EMAIL PROTECTED]> writes:
> Please advice me to fix this problem.
> Jun 20 18:38:41 lab5md9181 postgres[2552]: [2-1] 2005-06-20 18:38:41 EDT
> ERROR:
> unrecognized configuration parameter "ksqo"
KSQO has been obsolete for nigh five years now. Update whatever
software you
Martijn van Oosterhout wrote:
thnx a lot. But it does not work as expected cause the update-statement
ist not commiting for the whole table during the execution. So the
resulting order can be different from the original order, which is what
I try to avoid.
Well, that's because you're typin
On Tue, Jun 21, 2005 at 10:01:00AM -0400, Prasad Duggineni wrote:
> I did install successully postgres 8.03 but I am getting the following
> error message in the syslog eventhough /var/pgsql/data is pointing to new
> configuration file which is postgres 8.03.
> "ksqo" is configuration parameter
Hi,
I have a table with two indices on the same column, one of which is a partial index. I would like the query planner to use the partial index whenever the query condition lies in the range of the partial index as it would yield better performance. Is there any way to enforce the ordering for
Rohit Gaddi <[EMAIL PROTECTED]> writes:
> I have a table with two indices on the same column, one of which is a partial
> index. I would like the query planner to use the partial index whenever the
> query condition lies in the range of the partial index as it would yield
> better performance. I
On Tue, Jun 21, 2005 at 10:59:58AM +0200, [EMAIL PROTECTED] wrote:
> Hi Richard
>
> Thanks for the suggestion. I'm sure I'll go that way.
> One other question: Since in PostgreSQL you can have "overloaded"
> functions, how do you query the system tables for the existence of a
> particular version
Yuri Gordienko wrote:
Hi,
The computer with database has lost power and after restarting I can't
connect to database:
connection to database "db_client" failed: FATAL: invalid memory alloc
request size 4294901760
version:
postgresql-7.4.6
Help me with this problem. I don't have reserve copy
We have a script
process that needs to start a database, execute some SQL statements, then stop
the database. This is done as part of an application reset, in which we are
clearing out database data as part of the process. This is with 7.4.5 on Solaris
9/intel.
The problem we are
having i
Hey all
I need to join two tables and retrieve a 75 X 15 column matrix for all the
records in table 1 across a 13 week range so the output should look like
this. I would like the missing records in Table 2 to populate the structure
with 0.00...
Week à
Type Code
Descript
I have received very few replies to my suggestion that we implement E''
for escaped strings, so eventually, after a few major releases, we can
have '' treat backslashes literally like the SQL standard requires.
I assume this is because most people say, "yea, it is going to be a pain,
and yea, we
We have a system set up whereby postfix and maildrop gather user info
from a pg database (7.4.2 on FreeBSD 5.2.1) to do local mail acceptance
and delivery. I have configured max connections at 512 but I find that
this is not enough and I get "connection limit exceeded for
non-superusers" errors. I
Hi
Does anyone know of a free SQL Editor that allows you to debug PL/pgSQL
functions?
Thanks
Craig
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
You might want to look at pgEdit.
Sean
- Original Message -
From: "Craig Bryden" <[EMAIL PROTECTED]>
To: "pgsql"
Sent: Tuesday, June 21, 2005 3:01 PM
Subject: [GENERAL] Debugging PL/pgSQL
Hi
Does anyone know of a free SQL Editor that allows you to debug PL/pgSQL
functions?
Than
On Tuesday 21 June 2005 12:00 pm, Sven Willenberger wrote:
> We have a system set up whereby postfix and maildrop gather user
> info from a pg database (7.4.2 on FreeBSD 5.2.1) to do local mail
> acceptance and delivery. I have configured max connections at 512
> but I find that this is not enough
Keep in mind there is no built in API to debug PL/pgSQL like there is for PL/SQL. You will have to use the old true and tried output statements to debug your stored procs.
On 6/21/05, Sean Davis <[EMAIL PROTECTED]> wrote:
You might want to look at pgEdit.Sean- Original Message -From: "Craig
"David Parker" <[EMAIL PROTECTED]> writes:
> The problem we are having is that in a customer installation, the
> startup on the database is taking significantly longer than we have ever
> seen it take before.
Are we talking seconds, minutes, hours, days?
> But what I'm curious about is what set o
Ah, yes I should have qualified my
statement a bit. Sorry if I was misleading
Sean
- Original Message -
From:
Bob
To: Sean Davis
Cc: Craig Bryden ; pgsql
Sent: Tuesday, June 21, 2005 4:57
PM
Subject: Re: [GENERAL] Debugging
PL/pgSQL
Keep in min
Együd Csaba wrote:
Hi,
thank you very much. These are very good ideas, I think.
I forgot one thing to mention. We will have very few clients (max. 20) and
all clients will be required to have a fix IP address. Fix IP addresses can
be listed in pg_hba.conf to filter incoming IPs very efficiently
Együd Csaba wrote:
Hi Karl,
OK, I see the point. We are going to look around the VPN. So as a
conclusion: can we state, that, in addition to all the security features
postgres provides, applying a VPN - with SSL and firewal - is enough to
provide the necessary security?
No, they must be proper
Karl O. Pinc wrote:
On 06/20/2005 01:45:48 PM, Együd Csaba wrote:
Hi Karl,
OK, I see the point. We are going to look around the VPN. So as a
conclusion: can we state, that, in addition to all the security
features
postgres provides, applying a VPN - with SSL and firewal - is enough
to
provide
>> The problem we are having is that in a customer installation, the
>> startup on the database is taking significantly longer than we have
>> ever seen it take before.
>
>Are we talking seconds, minutes, hours, days?
It's in the seconds range, I think, probably not more than a minute, but
I don
On Tuesday June 21 2005 12:49 pm, Bruce Momjian wrote:
> I have received very few replies to my suggestion that we
> implement E'' for escaped strings, so eventually, after a few
> major releases, we can have '' treat backslashes literally
> like the SQL standard requires.
>
> I assume this is beca
On Tue, 2005-06-21 at 13:49 -0700, Steve Crawford wrote:
> On Tuesday 21 June 2005 12:00 pm, Sven Willenberger wrote:
> > We have a system set up whereby postfix and maildrop gather user
> > info from a pg database (7.4.2 on FreeBSD 5.2.1) to do local mail
> > acceptance and delivery. I have config
"Magnus Hagander" <[EMAIL PROTECTED]> writes:
> This patch removes Kerberos version 4 support from the backend and
> libpq. Per previous mail, I sent a mail to both hackers and -general
> about a month ago asking for ppl who use it, for zero responses. I also
> looked back in the archives and it se
# [EMAIL PROTECTED] / 2005-06-21 15:00:12 -0400:
> We have a system set up whereby postfix and maildrop gather user info
> from a pg database (7.4.2 on FreeBSD 5.2.1) to do local mail acceptance
> and delivery. I have configured max connections at 512 but I find that
> this is not enough and I get
Ed L. wrote:
> On Tuesday June 21 2005 12:49 pm, Bruce Momjian wrote:
> > I have received very few replies to my suggestion that we
> > implement E'' for escaped strings, so eventually, after a few
> > major releases, we can have '' treat backslashes literally
> > like the SQL standard requires.
>
I run postfix and have it connected to postgresql for just about
everything. Postfix is very sloppy on the database side, or so it seems.
I ended up having to configure postfix to limit the number of
processes it will start, and then make sure postgres has more than
that connections availab
Thanks for the proxymap tip. I will definitely look into it.
However, it probably won't do much for me, since I have user and
directory information (i.e. sensitive information) looked up, and
proxymap very clearly says not to use it for that. At least, not yet.
Though it will undoubtedly he
AM Software Design is proud to announce the 1.0
stable release of PG Lightning Admin for PostgreSQL 8.x.
PG Lightning Admin is a Windows GUI (graphical user interface)
administration program which will run on Windows 95,98, ME,NT 4SP6, 2000
and XP.
A full 30 day demo may be downloaded from:
http
On Jun 22, 2005, at 2:07 PM, Tony Caduto wrote:
AM Software Design is proud to announce the 1.0
stable release of PG Lightning Admin for PostgreSQL 8.x.
Shouldn't this be on pgsql-announce instead? According to its
description, pgsql-announce is an "Announcement list pertaining to
Postgre
59 matches
Mail list logo