> I want to store count(*) of a table in a variable ,
> for that I have declared a variable and wrote a
> statment but it is giving me error.
> can anybody please help me..
>
> CREATE OR REPLACE FUNCTION foreign_keys_tables(OUT
> par_result charecter varying, IN par_tablename
> character varying
[EMAIL PROTECTED] wrote:
> For example, the machine time is 11:46:xx, and I execute the SQL insert
> through the jdbc, the record time will be changed to 10:52:xx. I mean the
> time value is always slower than the machine time but not for a fixed time
> period.
>
> My java appserver is Sun Jav
Hello all,
I am trying to get the list of all foreign key tables
for a given primary table after getting this list I
want to filter out the name of table for which a
particular Primary table.primary key exsits in the
foreign table.foreign key column.
For that I have written a SP but the first curs
Hi,
Am Dienstag, 16. Jan 2007, 15:51:58 -0500 schrieb Tom Lane:
> Bertram Scharpf <[EMAIL PROTECTED]> writes:
> > Is there a deeper reason why the foreign key allows not
> > referenced non-null values
>
> The SQL spec says so. Use MATCH FULL to get the behavior you want.
Ah, I should have seen
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> I doubt Speakeasy is blocking anything. They are the ISP for geeks and say
> up front that they don't block ports and the like.
There are ISPs here that say the same think except for the geek part and are
known to block several ports.
> Did you use t
> That -- and make sure your "Secondary Logon" service is running. Also, to
> confirm, "Log On As" column in front of the pg service should mention the
> postgres
(low privileged) user.
Secondary logon service is only needed during installation. It's only used to
launch initdb.
/Magnus
I have a set of tables with one-to-many relationships between them:
T1 <-->> T2 <-->> T3 <-->> T4
I need to copy some rows of these tables to another set of tables which have
the same fields. There are two rows on T1 that I want to copy, and then
those rows of T2 linked to the other ones, and
Hi,
I'm using Postgresql 8.1 on windows2000 and I have a hard time
understanding how to limit the memory of the sessions to 1 MB.
What I have right now is that each connection (opened with jdbc) takes
about 3MB (some take a little more, some a little less). I think that
this is a waste of memory
James Im wrote:
Hi,
I'm using Postgresql 8.1 on windows2000 and I have a hard time
understanding how to limit the memory of the sessions to 1 MB.
Well, you don't explicitly, but see below.
What I have right now is that each connection (opened with jdbc) takes
about 3MB (some take a little mo
Toni Casueps wrote:
I have a set of tables with one-to-many relationships between them:
T1 <-->> T2 <-->> T3 <-->> T4
I need to copy some rows of these tables to another set of tables which
have the same fields.
There's no shortcut.
BEGIN;
INSERT INTO copy_t1 SELECT * FROM t1 WHERE id IN (
Hello,
I'm new to postgreSQL (not databases) and trying to find my way arouund.
Have a couple of questions:
(a) In which dir is the pg_global stored?
Is it "C:\Program Files\PostgreSQL\8.2\data\global"?
(b) Why is the pg_tablespace.spclocation column=null for pg_default &
pg_global?
(c) When
On Tue, 2007-01-16 at 20:00 -0600, Adam Rich wrote:
> Robert,
> Open "Computer Management" and find the postgres service.
> There should be an item saying "Run As". You want that to be
> a non-privledged account. Typically, postgres asks you what account
> to use when you install it. Just make
Robert Fitzpatrick wrote:
Thanks, yes, I verified the services has 'postgres' as the account being
used under the Log On tab of the PostgreSQL service. But still, the
service will not start with the administrative permissions error
previously posted. Any other ideas?
Did you check that the "po
Raymond O'Donnell wrote:
> Robert Fitzpatrick wrote:
>
>> Thanks, yes, I verified the services has 'postgres' as the account being
>> used under the Log On tab of the PostgreSQL service. But still, the
>> service will not start with the administrative permissions error
>> previously posted. Any ot
On Wed, 2007-01-17 at 14:13 +, Dave Page wrote:
> Raymond O'Donnell wrote:
> > Robert Fitzpatrick wrote:
> >
> >> Thanks, yes, I verified the services has 'postgres' as the account being
> >> used under the Log On tab of the PostgreSQL service. But still, the
> >> service will not start with t
We just did a bunch of maintenance on one of our production databases that
involved a lot of alter tables and moving records about and the like.
Afterwards, I did a vacuum full and analyze to get the database back on
track -- autovac maintains it under normal operations.
Today I decided to run r
Richard Huxton writes:
> The time is fixed at the start of the transaction. This lets you do
> several inserts having the same timestamp.
I think there's another problem here, which is that he's declared
"currenttime" as a timestamp without time zone, but the
CURRENT_TIMESTAMP function yields tim
Richard Huxton writes:
> James Im wrote:
>> What am I missing to limit the memory taken by session to 1MB?
> You can't. In particular, work_mem is memory *per sort* so can be
> several times that. If you're trying to get PG to run in 64MB or
> something like that, I think you're going to be dis
I have a process that appears to hang every night. I ran the following
query and results, and it looks like an autoanalyze and query are
waiting on a lock that's being exclusively held by a transaction that is
IDLE. Any ideas? Any additional queries I should run to shed light on
the issue?
SEL
Bill Moran wrote:
>
> We just did a bunch of maintenance on one of our production databases that
> involved a lot of alter tables and moving records about and the like.
>
> Afterwards, I did a vacuum full and analyze to get the database back on
> track -- autovac maintains it under normal operati
I have a servlet which gets its data through a DataSource (Tomcat 5.5)
and starts each request as a new SQL transaction and either commits
the transaction or rolls it back at the end of each request.
In one of the requests I do a SELECT ... INTO TEMPORARY t1 ..., which
works just fine when I firs
In response to Alvaro Herrera <[EMAIL PROTECTED]>:
> Bill Moran wrote:
> >
> > We just did a bunch of maintenance on one of our production databases that
> > involved a lot of alter tables and moving records about and the like.
> >
> > Afterwards, I did a vacuum full and analyze to get the datab
Hi,
I'm having some problems with creating an odbc connection between postreSQL
8.2 and a multi-value database (Univision) -. I have 2 situations, one that
works, and one that doesn't - unfortunately I need to use the one that
doesn't.
Situation 1.
I have Univision loaded onto my PC runni
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> Today I decided to run reindex during a slow period, and was shocked to
> find the database size drop from 165M to 30M. Keep in mind that the
> 165M is after vacuum full. So, apparently, there was 135M of index bloat?
> That seems a little excess
[snip]
> Come to think of it, an auto-reindex option might be nice in core someday.
> TODO item?
Marry it with autovacuum + online index build, and it will be cool ;-)
BTW, having a privileged background thread doing the reindex could be a
solution to most of the objections regarding online reind
David Goodenough <[EMAIL PROTECTED]> writes:
> In one of the requests I do a SELECT ... INTO TEMPORARY t1 ..., which
> works just fine when I first use it, but from then on it objects saying
> that t1 already exists. When I read the documentation (8.1 as that is
> what I am using) I thought I un
David Goodenough wrote:
I thought I understood that the table would disappear
at the end of the transaction. Other than deleting it, is there something
else I need to do or have I missunderstood that into temporary does?
Dropped when the connection is closed normally, but see the ON COMMIT.
Bill Moran <[EMAIL PROTECTED]> writes:
> The entire database was around 28M prior to the upgrades, etc. Immediately
> after the upgrades, it was ~270M. Following a vacuum full, it dropped to
> 165M. Following a database-wide reindex, it dropped to 30M.
As Alvaro said, vacuum full doesn't shrink
Bill Moran wrote:
> The entire database was around 28M prior to the upgrades, etc. Immediately
> after the upgrades, it was ~270M. Following a vacuum full, it dropped to
> 165M. Following a database-wide reindex, it dropped to 30M.
Oh, so it was clearly the upgrade procedure that caused the bl
While using third party modules is not really a barrier for people
deep in the PostgreSQL world, here is what I would need to do to use
GUIDs today.
1. Get a set of GUID/UUID patches (there seem to be many) and
recompile the necessary code.
2. Do the same thing for Windows, assuming that the pa
am Wed, dem 17.01.2007, um 16:04:10 + mailte David Goodenough folgendes:
> I have a servlet which gets its data through a DataSource (Tomcat 5.5)
> and starts each request as a new SQL transaction and either commits
> the transaction or rolls it back at the end of each request.
>
> In one of
Richard Huxton wrote:
> Is there a particular problem you're trying to solve?
yes I'm trying to know how many connections can open to the database
without running out of memory. Ideally I would like to optimize stuff
so that I can open the maximum number of connection/session.
In total I can gi
On Wed, Jan 17, 2007 at 07:54:55 -0200,
Jorge Godoy <[EMAIL PROTECTED]> wrote:
> Bruno Wolff III <[EMAIL PROTECTED]> writes:
>
> The don't block a host I used to access. And not on several different
> Brazilian networks from different carriers. The traffic stops at speakeasy
> from my house (A
On 1/17/07, Patrick Earl <[EMAIL PROTECTED]> wrote:
Certainly support for the GUID field type itself is most important.
As for the generators, though they are non-essential, they are very
useful. Other platforms and libraries have standardized on uuid
generators, so I don't see why PostgreSQL c
Hello,
I would like to ask if there is a genral concept of creating web pages using
a database and PHP or another scripting language.
What I mean is to store basic entities and relations between pages. Only the
certain texts or information regarding every site will differ. But i
There is another blog article comparing postgresql with mysql, where the
postgres is slow thing is repeated and the benchmark tends to confirm. The
blogger is new to postgres and does not represent the test as definative.
http://wskills.blogspot.com/2007/01/postgresql-vs-mysql-benchmark.html
Fir
In response to Alvaro Herrera <[EMAIL PROTECTED]>:
> Bill Moran wrote:
>
> > The entire database was around 28M prior to the upgrades, etc. Immediately
> > after the upgrades, it was ~270M. Following a vacuum full, it dropped to
> > 165M. Following a database-wide reindex, it dropped to 30M.
>
On 1/17/07, Jan Mura <[EMAIL PROTECTED]> wrote:
I would like to ask if there is a genral concept of creating web pages
using
a database and PHP or another scripting language.
What I mean is to store basic entities and relations between pages. Only
the
certain texts or information regarding ever
James Im wrote:
Richard Huxton wrote:
> Is there a particular problem you're trying to solve?
yes I'm trying to know how many connections can open to the database
without running out of memory. Ideally I would like to optimize stuff
so that I can open the maximum number of connection/session.
Jan Mura wrote:
Hello,
I would like to ask if there is a genral concept of creating web pages using
a database and PHP or another scripting language.
The term you'll want to search on is "Content Management System" or CMS.
There are plenty to choose from: Plone, Zope, Mambo
In response to Ben <[EMAIL PROTECTED]>:
> Hey Bill. How do you monitor your shared buffer usage? My understanding
> was that there wasn't a good way to see what was used vs. allocated.
echo "select count(*) from pg_buffercache where reldatabase is not null;" |
$PSQL_BIN -P tuples_only -U pgsql
On Wed, 2007-01-17 at 11:02 +, James Im wrote:
> Hi,
>
> I'm using Postgresql 8.1 on windows2000 and I have a hard time
> understanding how to limit the memory of the sessions to 1 MB.
>
> What I have right now is that each connection (opened with jdbc) takes
> about 3MB (some take a little m
On Wed, Jan 17, 2007 at 09:41:03AM -0800, novnov wrote:
>
> There is another blog article comparing postgresql with mysql, where the
> postgres is slow thing is repeated and the benchmark tends to confirm. The
> blogger is new to postgres and does not represent the test as definative.
>
> http://
Jan Mura wrote:
Hello,
I would like to ask if there is a genral concept of creating web pages
using
a database and PHP or another scripting language.
What I mean is to store basic entities and relations between pages.
Only the
certain texts or information regarding every s
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> Be sure you aren't blocking the return traffic. I did that once using a /8
As I said, just if I did that on several Brazilian networks. There's no
blocking on any of these networks.
> You might also check to see if the ip address on your end was rec
On Wed, Jan 17, 2007 at 07:02:13PM +0530, Alexi Gen wrote:
> Hello,
>
> I'm new to postgreSQL (not databases) and trying to find my way arouund.
> Have a couple of questions:
>
> (a) In which dir is the pg_global stored?
> Is it "C:\Program Files\PostgreSQL\8.2\data\global"?
Yes.
> (b) Why is t
Hi all,
pg_dump and pg_dumpall have the -o option that should tell them to
include oids in dump. I didn't chose this option, and the dump doesn't
include "WITH OIDS", but the tables are created with oids when restoring
this dump.
I'm dumping from 7.4.5 to 8.2.1.
I do have
#default_with_oids = off
Well, you could always start with something like Drupal:
http://www.drupal.com
I get access denied, seems to be here, rather: drupal.org
- John Burger
MITRE
---(end of broadcast)---
TIP 6: explain analyze is your friend
Tom Lane wrote:
> Bill Moran <[EMAIL PROTECTED]> writes:
>> The entire database was around 28M prior to the upgrades, etc. Immediately
>> after the upgrades, it was ~270M. Following a vacuum full, it dropped to
>> 165M. Following a database-wide reindex, it dropped to 30M.
>
> As Alvaro said, v
=?ISO-8859-1?Q?St=E9phane_Schildknecht?= <[EMAIL PROTECTED]> writes:
> pg_dump and pg_dumpall have the -o option that should tell them to
> include oids in dump. I didn't chose this option, and the dump doesn't
> include "WITH OIDS", but the tables are created with oids when restoring
> this dump.
I receive this message when I do a specific operation.
How can I debug something like this?
I use a Centos 4.0 distribution, postgresql version 8.1.3(I also used
diffrent distibutions and other versions of postgresql).
I connect to postgresql using perl DBD::Pg, using a tcp socket.
The error a
On Jan 17, 2007, at 11:56 AM, Tom Lane wrote:
So the above doesn't sound too unlikely. Perhaps we should recommend
vac full + reindex as standard cleanup procedure. Longer term, maybe
teach vac full to do an automatic reindex if it's moved more than X
% of
a vac full + reindex is a waste o
Some handy links for you:
http://www.sun.com/software/solaris/howtoguides/postgresqlhowto.jsp
http://docs.sun.com/app/docs/doc/819-5578?q=+postgresql&a=expand
http://www.lethargy.org/~jesus/archives/77-Choosing-Solaris-10-over-Linux.html
On Saturday 13 January 2007 01:26, Gene wrote:
> thanks for
Sterpu Victor <[EMAIL PROTECTED]> writes:
> I receive this message when I do a specific operation.
> How can I debug something like this?
It sounds like DBD::Pg is sending an invalidly formed message, or in
some other way violating the protocol. Or maybe the backend got
confused. Watching the se
I'm kind of confused how this require thing would actually work
because I tried testing it at my end at its really not working with
postgres.
I'm sure there is some fundamental mistake.
I have to do the following:
I have a perl file and need to call and use full functionaily of
this perl file i
Brazil has been trying to get its ISPs to block access to a certain
video. I wonder if too wide a net was cast in that effort.
http://www.slate.com/id/2157399/?nav=navoa
On 1/17/07, Jorge Godoy <[EMAIL PROTECTED]> wrote:
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> Be sure you aren't blockin
On 1/17/07, Richard Huxton wrote:
Toni Casueps wrote:
>
> I have a set of tables with one-to-many relationships between them:
>
> T1 <-->> T2 <-->> T3 <-->> T4
>
> I need to copy some rows of these tables to another set of tables which
> have the same fields.
There's no shortcut.
BEGIN;
INSERT
Hi,
Thanks for the reply.
I still work on it, but i guess this problem may on my application side not the
db server. I mean the appserver connection pool or jdbc configuration. I still
can not nail it. Because I have another web application with the same iBATIS
jdbc configuration to the appserv
Dear Sirs,
my question is very simple:
when I insert a row whith a serial field, a value is automatically
generated; how can I know this value, strictly of my row, without the risk
of to read the value of another subsequent insertion?
Thank you.
Domenico
---(end of br
Hello,
I'm new to postgreSQL (not databases) and trying to find my way arouund.
Have a couple of questions:
(a) In which dir is the pg_global stored?
Is it "C:\Program Files\PostgreSQL\8.2\data\global"?
(b) Why is the pg_tablespace.spclocation column=null for pg_default &
pg_global?
(c) When
On Mon, Jan 01, 2007 at 18:46:26 +0100,
dfx <[EMAIL PROTECTED]> wrote:
> Dear Sirs,
>
> my question is very simple:
> when I insert a row whith a serial field, a value is automatically
> generated; how can I know this value, strictly of my row, without the risk
> of to read the value of another
dfx wrote:
Dear Sirs,
my question is very simple:
when I insert a row whith a serial field, a value is automatically
generated; how can I know this value, strictly of my row, without the risk
of to read the value of another subsequent insertion?
Thank you.
Domenico
Hiya,
Not sure if it w
If it's a recent PG:
select lastval()
See this:
http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of dfx
Sent: Monday, January 01, 2007 11:46 AM
To: pgsql-general@postgresql.org
Subject
I'm working on a voicemail application which involves storing phone
numbers and other types that have long sequences of digits [0-9]. I
need to be able to do pattern matching using LIKE and map the fields
back to java String objects. I was thinking maybe it would be more
efficient to use the numer
On Wed, Jan 17, 2007 at 05:34:27PM -0500, Tom Lane wrote:
> Sterpu Victor <[EMAIL PROTECTED]> writes:
> > I receive this message when I do a specific operation.
> > How can I debug something like this?
>
> It sounds like DBD::Pg is sending an invalidly formed message, or in
> some other way violat
On Thu, Jan 18, 2007 at 01:15:02AM -0500, Gene wrote:
> I'm working on a voicemail application which involves storing phone
> numbers and other types that have long sequences of digits [0-9]. I
> need to be able to do pattern matching using LIKE and map the fields
> back to java String objects. I w
On Jan 18, 2007, at 15:15 , Gene wrote:
My calculations for disk space based off some information i found
online are ( 8 + ( 2 bytes for every four digits) ) for numeric and (
4 + number of chars ) for a utf8 varchar datatype. Are these
calculations still valid and has anyone tried using numeri
you're probably right, actually using LIKE with numeric works fine but
of course i will have to contend with things like truncating zeros to
the left etc... are there any advantages to using ascii encoding as
far as performance of LIKE, REGEXES, INDEXES etc versus using UTF8?
thanks, gene
On 1/1
Hello I'm a newbie for PostgreSQL
I want to used a BLOB data
I read Postgre's Manual that make, used at large Object or BLOB used other
library
libpq/libpq-fs.h
I search it, but I cannot find it...
Where can I download header file and dll file
-
8:00?
Gene schrieb:
you're probably right, actually using LIKE with numeric works fine but
of course i will have to contend with things like truncating zeros to
the left etc... are there any advantages to using ascii encoding as
far as performance of LIKE, REGEXES, INDEXES etc versus using UTF8?
Well
Bill Moran <[EMAIL PROTECTED]> writes:
> It wasn't the fact that it bloated that surprised me. It was the
> _magnitude_ of bloat that I wasn't expecting, as well as the fact that
> it was _all_ _index_ bloat.
Um, no, you had plenty of table *and* index bloat before. The problem
here is that VACU
Ok so the solution is to limit the number of connections. But it seems
that there is no good way to choose the ideal number of connections as I
don't know how much RAM will a connection use.
If a connection takes 3MB (on windows I see the process in the Process
Monitor, in Linux the RSS is more l
72 matches
Mail list logo
