Tony Caduto wrote:
[question about finding data directory]
I found it in the pg_settings view, but if there is another way I would
like to know about it.
As Tom aluded, you can also do this:
regression=# select current_setting('data_directory');
current_setting
--
Hi Martijn van Oosterhout ,
This is the output that I get by running the query
SELECT datname, age(datfrozenxid) FROM pg_database;
datname | age
+--
MyProd | 10014107
MyProdtest | 10014107
template1 | 10014107
template0 | 10014107
MyDb | 10014107
(5 rows)
Re
Tony Caduto <[EMAIL PROTECTED]> writes:
> Does anyone know if there is a function that can return the path to the
> data directory?
In 8.0 and later,
SHOW data_directory;
or the equivalent function call.
regards, tom lane
---(end of broadc
I found it in the pg_settings view, but if there is another way I would
like to know about it.
Thanks,
Tony
Tony Caduto wrote:
Does anyone know if there is a function that can return the path to
the data directory?
I was looking through the docs, but could not find anything.
Thanks,
--
Does anyone know if there is a function that can return the path to the
data directory?
I was looking through the docs, but could not find anything.
Thanks,
--
Tony Caduto
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql 8.x
---(end of broad
Steven Brown <[EMAIL PROTECTED]> writes:
> I'm granting access to insert/update/delete rows of a table to people,
> but I don't want all future inserts to fail if they decided to change
> an id (which they obviously shouldn't, but they /can/). It makes for
> a fragile system.
If it shouldn't hap
Steven Brown wrote:
When I change an id (primary key serial) in a table, the next value
returned by the sequence for the id can conflict with that id (e.g.,
change the id to be id + 1). MySQL seems to handle this transparently
by skipping conflicting values, but with PostgreSQL I get primary
Tom Lane wrote:
Steven Brown <[EMAIL PROTECTED]> writes:
When I change an id (primary key serial) in a table, the next value
returned by the sequence for the id can conflict with that id (e.g.,
change the id to be id + 1).
[...]
Plan A: don't do that. Why in the world is it a good idea to mo
Hi all,
I cannot find what is the problem with my function below. The
following line in the function :AND PD.PDPONUM = ANY (receivingIds)
don't work. If I change this line byAND PD.PDPONUM = 1734 (Hardcode
a given value) I get a result row.
When I call the same function
select
Curiously none are security reasons, they are more portability reasons
(and pretty thin ones at that)... but then this is PHP we are talking
about - let me just say register_globals and end it there.
I would have to say that for security purposes - I would want magic
quotes _on_ rather than off fo
Steven Brown <[EMAIL PROTECTED]> writes:
> When I change an id (primary key serial) in a table, the next value
> returned by the sequence for the id can conflict with that id (e.g.,
> change the id to be id + 1). MySQL seems to handle this transparently
> by skipping conflicting values, but wit
When I change an id (primary key serial) in a table, the next value
returned by the sequence for the id can conflict with that id (e.g.,
change the id to be id + 1). MySQL seems to handle this transparently
by skipping conflicting values, but with PostgreSQL I get primary key
conflicts. It se
On Wed, Nov 02, 2005 at 03:39:24PM -0800, Patrick Hatcher wrote:
>
>
> I need to generate a data dictionary for all my tables (name, column,
> type, etc) in my database. Is there an easy to do this without having to
> do a \d tablename for each table?
You could use pg_dump -s to get the schem
On Wed, Nov 02, 2005 at 05:26:56PM -0700, Trent Shipley wrote:
> I never really used SQL*Plus as a command line tool. I tended to use it as a
> weak SQL scripting language.
>
> Granted report generators can't be part of core PostgreSQL, they are still a
> critical part of any database worksho
> ===
>
> A) Are there any FOSS SQL scripting tools that output data ready for
> reporting
> (like SQR, but better)?
Open Office Base, OpenMFG, Jasper ...
>
> B) Are there any FOSS tools that will take data and build pretty output.
See above.
>
> C) Are there any FOSS tools of ty
http://www.oreillynet.com/pub/wlg/8274
Check it out folks.
--
Aly Dharshi
[EMAIL PROTECTED]
"A good speech is like a good dress
that's short enough to be interesting
and long enough to cover the subject"
---(end of broadcast)---
On Wednesday 2005-11-02 13:11, Jim C. Nasby wrote:
> On Wed, Nov 02, 2005 at 01:25:31PM -0600, James Thompson wrote:
> > > Yes, sqlplus looks especially bad once you're used to banging around
> > > in psql. Although, I recently discovered rlwrap (a generic readline
> > > wrapper) which makes sqlpl
Bruce Momjian writes:
> Jim C. Nasby wrote:
>> Would it be feasable to have the lock manager spew out info about lock
>> aquisition and release? Not only would it make getting this information
>> easy, but I suspect it could be a useful debugging tool.
> Something like log_locks? That would be v
On Nov 2, 2005, at 6:08 PM, Michael Glaesemann wrote:
As an aside, it's interesting to see that the PHP documentation states:
---
Magic Quotes is a process that automagically escapes incoming data to
the PHP script. It's preferred to code with magic quotes off and to
instead escape the data a
Thank you.
"select * From information_schema.columns where table_schema = 'public' and
table_name = 'mdc_products'
order by ordinal_position;"
Thanks again.
Patrick Hatcher
"Dann Corbit"
Carlos,
What you are asking for is a multi-master replication scheme. Slony-I
is a single master system, meaning that for each slony cluster only one
node can make changes to the data.
Without breaking slony's normal rules, I believe that there might be a
way to do it, though it will not be prett
PostgreSQL has Information Schema
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Patrick Hatcher
> Sent: Wednesday, November 02, 2005 3:39 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Data Dictionary generator?
>
>
>
I need to generate a data dictionary for all my tables (name, column,
type, etc) in my database. Is there an easy to do this without having to
do a \d tablename for each table?
tia
Patrick
---(end of broadcast)---
TIP 3: Have you checked our ex
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> That's because unfortunately PostgreSQL only keeps statistics on
> individual columns. There's no stats kept on multi-column indexes; the
> best the planner can do is use the stats for the first column.
That's not what we do at all: we do look at the st
Jim C. Nasby wrote:
> On Wed, Nov 02, 2005 at 05:59:15PM -0500, Bruce Momjian wrote:
> > Actually, pre-8.1 used ROW EXCLUSIVE for foreign keys because we didn't
> > have ROW SHARE until 8.1. I actually can't find out how we are doing
> > that in the code, however. Analyzing the code is probably t
On Wed, Nov 02, 2005 at 05:45:40PM -0500, Andrew Sullivan wrote:
> On Wed, Nov 02, 2005 at 12:06:36PM +, Carlos Benkendorf wrote:
> > I would appreciate suggestions about how the best way to implement
> > such soluction.
> >
> > Slony-1? SQL scripts?
>
> Maybe a combination. My natural incl
On Wed, Nov 02, 2005 at 05:59:15PM -0500, Bruce Momjian wrote:
> Actually, pre-8.1 used ROW EXCLUSIVE for foreign keys because we didn't
> have ROW SHARE until 8.1. I actually can't find out how we are doing
> that in the code, however. Analyzing the code is probably the only way
> to get this de
On Nov 3, 2005, at 4:26 , Alex Turner wrote:
My point is that with magic_quotes on in PHP, php already escapes
quotes for you in all inbound variables. This makes the process
automatic, and therefore fool proof, which is kinda the whole point.
You want a mechanism that there isn't an easy way
Thomas F. O'Connell wrote:
> I thought about posting to pgsql-docs, but since this might require
> comment from developers, I thought -general might be a better
> starting point.
>
> Anyway, I've occasionally run into monitoring situations where it
> would be immediately helpful to know the
On Wed, Nov 02, 2005 at 12:06:36PM +, Carlos Benkendorf wrote:
> I would appreciate suggestions about how the best way to implement
> such soluction.
>
> Slony-1? SQL scripts?
Maybe a combination. My natural inclination would be to try to do
this with some tricky views+rules so that each st
On Wed, Nov 02, 2005 at 10:55:36PM +0100, MaXX wrote:
> Jim C. Nasby wrote:
> > On Wed, Nov 02, 2005 at 08:50:45PM +0100, MaXX wrote:
> [...]
> >> In simple words:
> >> Clustered indexes are like the alphabetical index in a book, where term
> >> are randomly distibuted in the book and regular index
I thought about posting to pgsql-docs, but since this might require
comment from developers, I thought -general might be a better
starting point.
Anyway, I've occasionally run into monitoring situations where it
would be immediately helpful to know the built-in SQL statements that
generat
Scott Marlowe wrote:
> On Wed, 2005-11-02 at 13:50, MaXX wrote:
[...]
>> In simple words:
>> Clustered indexes are like the alphabetical index in a book, where term
>> are randomly distibuted in the book and regular indexes are more like the
>> table of content...
>> Right?
> Not really. It's more
Postgresql 8.0.4 using plpgsql
The basic function is set up as:
CREATE FUNCTION add_data(t_row mytable) RETURNS VOID AS $func$
DECLARE
newtable text;
thesql text;
BEGIN
INSERT INTO newtable thename from mytable where lookup.id =
t_row.id;
thesql := 'INSERT INTO ' || newtable || VAL
> >
> > I haven't used Oracle since the mid 90s so I don't have a working example
> > but a description of some of the commands can be found here
> >
> > http://www.siue.edu/~dbock/cmis564/otext3.htm
>
> Given the choice, I'd *MUCH* rather have a good, easy-to-use CLI than a
> reporting tool. I'm
Jim C. Nasby wrote:
> On Wed, Nov 02, 2005 at 08:50:45PM +0100, MaXX wrote:
[...]
>> In simple words:
>> Clustered indexes are like the alphabetical index in a book, where term
>> are randomly distibuted in the book and regular indexes are more like the
>> table of content...
>> Right?
> You have t
Why, when I create a new database owned by a specified user (createdb
--owner=somebody), would the public schema in that database not be owned
by the user and moreover not be writable by the user? I'm using a fresh
install of 8.1rc1, but the same thing seems to happen with an unfresh 8.0.
Tha
On Wed, Nov 02, 2005 at 08:50:45PM +0100, MaXX wrote:
> Ok thank you,
> so I can consider using clustered indexes when I need to 'reorder' random
> data to improve the speed of a particular query...
>
> In simple words:
> Clustered indexes are like the alphabetical index in a book, where term are
On Wed, Nov 02, 2005 at 01:25:31PM -0600, James Thompson wrote:
> > Yes, sqlplus looks especially bad once you're used to banging around
> > in psql. Although, I recently discovered rlwrap (a generic readline
> > wrapper) which makes sqlplus almost tolerable. It's the best thing to
> > happen to
On Wed, 2005-11-02 at 13:50, MaXX wrote:
> Ok thank you,
> so I can consider using clustered indexes when I need to 'reorder' random
> data to improve the speed of a particular query...
>
> In simple words:
> Clustered indexes are like the alphabetical index in a book, where term are
> randomly di
On Wednesday 02 November 2005 08:57, YL wrote:
> I tried but have very little progress on this. If any one know where to
> find an multi-language example (php prefered) using postgresql, please let
> me know. Thanks
> [EMAIL PROTECTED]
phppgadmin runs in a slew of languages against a slew of encod
Ok thank you,
so I can consider using clustered indexes when I need to 'reorder' random
data to improve the speed of a particular query...
In simple words:
Clustered indexes are like the alphabetical index in a book, where term are
randomly distibuted in the book and regular indexes are more like
My point is that with magic_quotes on in PHP, php already escapes
quotes for you in all inbound variables. This makes the process
automatic, and therefore fool proof, which is kinda the whole point.
You want a mechanism that there isn't an easy way around, like
forgetting to db_quote once in a wh
> Yes, sqlplus looks especially bad once you're used to banging around
> in psql. Although, I recently discovered rlwrap (a generic readline
> wrapper) which makes sqlplus almost tolerable. It's the best thing to
> happen to sqlplus since... well, since "quit" I suppose.
I just wish pgsql had so
On Nov 1, 2005, at 5:12 PM, Jim C. Nasby wrote:
My understanding is that there is no 32 bit version of FBSD on
Opterons;
as soon as buildworld sees it's on an Opteron everything goes 64 bit.
you would understand incorrectly, then. freebsd will never flip you
up to 64 bit world like that.
I very much wish to thank Oliver and Michael for their help in getting
started. I was scratching around the edges of trigger functions without being
able to put everything in its proper place.
I now have a way of moving forward with some glimmer of eventually knowing
what I am doing.
Than
The key expense in doing an index scan is the amount of randomness
involved in reading the base table. If a table is in the same order as
the index then reading the base table will be very fast. If the table is
in a completely random order compared to an index (it's correlation is
low), then an ind
Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Wed, Nov 02, 2005 at 06:07:45PM +0100, A. Kretschmer wrote:
>> is 'record_out()' new in 8.1?
> The signature has changed over time:
> 7.3 record_out(record)
> 7.4 record_out(record)
> 8.0 record_out(record,oid)
> 8.1 record_out(record)
BTW, the a
Michael Fuhr wrote:
> On Wed, Nov 02, 2005 at 06:07:45PM +0100, A. Kretschmer wrote:
> > am 02.11.2005, um 9:35:33 -0700 mailte Michael Fuhr folgendes:
> > > test=> SELECT id, md5(textin(record_out(foo))) FROM foo;
> >
> > is 'record_out()' new in 8.1?
>
> The signature has changed over time:
>
Hugo wrote:
Hi, is anybody using psql and CentOS, I just wanted to know your
experience
thanks
Hugo
Works great, could not be happier.
I am also running one CentOS server in 64bit mode, I built Postgres from
source on this box because I could not find a RPM at the time.
No problems and t
On Wed, Nov 02, 2005 at 06:07:45PM +0100, A. Kretschmer wrote:
> am 02.11.2005, um 9:35:33 -0700 mailte Michael Fuhr folgendes:
> > test=> SELECT id, md5(textin(record_out(foo))) FROM foo;
>
> is 'record_out()' new in 8.1?
The signature has changed over time:
7.3 record_out(record)
7.4 recor
On Wed, Nov 02, 2005 at 12:18:15PM -0500, Tom Lane wrote:
> Michael Fuhr <[EMAIL PROTECTED]> writes:
> > I just noticed that record_out(foo) works only in 8.1. When I have
> > more time I'll see if it's possible in earlier versions.
>
> Probably not :-(
This works in 8.0.4, although it gives eve
On Wed, Nov 02, 2005 at 02:49:57PM -0200, Jon Lapham wrote:
> Michael Fuhr wrote:
> >test=> SELECT id, foo FROM foo;
> > id | foo
> >+-
> > 1 | (1,123,"this is a test",2005-11-02,t,"\\0
Michael Fuhr <[EMAIL PROTECTED]> writes:
> I just noticed that record_out(foo) works only in 8.1. When I have
> more time I'll see if it's possible in earlier versions.
Probably not :-(
2005-05-04 20:19 tgl
* src/backend/parser/parse_coerce.c: Allow implicit cast from any
named
am 02.11.2005, um 9:35:33 -0700 mailte Michael Fuhr folgendes:
> test=> SELECT id, md5(textin(record_out(foo))) FROM foo;
is 'record_out()' new in 8.1?
Regards, Andreas
--
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://ww
On Wed, Nov 02, 2005 at 09:35:33AM -0700, Michael Fuhr wrote:
> test=> SELECT id, md5(textin(record_out(foo))) FROM foo;
> id | md5
> +--
> 1 | b1cbe3d5ed304f31da57b85258f20c8f
I just noticed that record_out(foo) works only in 8.
Michael Fuhr wrote:
test=> SELECT id, foo FROM foo;
id | foo
+-
1 | (1,123,"this is a test",2005-11-02,t,"\\000\\001\\002")
Perfect! Wow, in all these years of using PostgreSQL,
In article <[EMAIL PROTECTED]>,
Douglas McNaught <[EMAIL PROTECTED]> writes:
> Rory Browne <[EMAIL PROTECTED]> writes:
>> select u.username, g.groupname from users u, groups g where u.group_id=g.id
>> (assuming users are in exactly one group)
>>
>> If the group_id field in the users table was cor
On Wed, Nov 02, 2005 at 11:38:46AM -0200, Jon Lapham wrote:
> I would love something like this:
> select id, md5(*) from mytable;
Is it acceptable to have some decoration around the data being
hashed? If so then this example might be useful:
test=> SELECT * FROM foo;
id | integer | text
On Wed, 2005-11-02 at 08:40, Hugo wrote:
> Hi, is anybody using psql and CentOS, I just wanted to know your
> experience
We use Centos (RHEL 3 and 4 flavors) as our internal server os with
postgresql 7.4.7 / 7.4.8 here. It works a charm.
---(end of broadcast)
Just installed and tuned 8.0. tested with 100 connections and a 7.5 GB database, works like a champ.
new pl/perl is a beauty to write triggers on 8.0, check it out.
On 11/2/05, Steve Wampler <[EMAIL PROTECTED]> wrote:
Hugo wrote:> Hi, is anybody using psql and CentOS, I just wanted to know your e
On Wed, Nov 02, 2005 at 10:28:56AM -0500, Tom Lane wrote:
> Looking at the code, I think that actually a regular, non-FREEZE VACUUM
> would do the "right thing" for tuples up to about 1 billion xacts past
> wrap, which is probably enough. So the answer may be "just VACUUM".
> I'm still too lazy to
Martijn van Oosterhout writes:
> On Wed, Nov 02, 2005 at 09:46:38AM -0500, Tom Lane wrote:
>> You sure about that? I think VACUUM just tests for "committed or not".
>>
>> I'm too lazy to set up a test case, but it's possible that VACUUM FREEZE
>> would resurrect wrapped-around tuples, or could b
Hugo wrote:
> Hi, is anybody using psql and CentOS, I just wanted to know your experience
Works just fine, though we're still on 7.4 and haven't tried the 8 series
yet. I don't expect any problems, however.
--
Steve Wampler -- [EMAIL PROTECTED]
The gods that smiled on your birth are now laughi
On Wed, Nov 02, 2005 at 09:46:38AM -0500, Tom Lane wrote:
> Martijn van Oosterhout writes:
> > The really nasty thing about it is that because the records are now
> > considered really old, as soon as you do run VACUUM it'll start
> > removing the rows you want to save...
>
> You sure about that?
Martijn van Oosterhout writes:
> The really nasty thing about it is that because the records are now
> considered really old, as soon as you do run VACUUM it'll start
> removing the rows you want to save...
You sure about that? I think VACUUM just tests for "committed or not".
I'm too lazy to s
Hi, is anybody using psql and CentOS, I just wanted to know your experience
thanks
Hugo
Rory Browne <[EMAIL PROTECTED]> writes:
> select u.username, g.groupname from users u, groups g where u.group_id=g.id
> (assuming users are in exactly one group)
>
> If the group_id field in the users table was corrupted, and set to a
> value that isn't in the groups table, then that view wouldn't
On 11/1/05, Andrew Rawnsley <[EMAIL PROTECTED]> wrote:
>
> They actually did make _some_ strides. The installer actually works
> consistently (knock on veneer-covered-pressboard), which is something I
> haven't seen since the pre-8i text-mode installs...
>
> Doesn't quite compare to the 5 minute un
On Tue, Nov 01, 2005 at 11:33:39PM -0700, Mike Shelton wrote:
> Hello,
>
> I implemented connection pooling in the backend of postgresql (one of the
> TODO items) and I've found some really interesting performance improvements
> I'd like to continue to explore but unfortunately I've also uncovered
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of
> Marc G. Fournier
> Sent: 31 October 2005 04:48
> To: pgsql-announce@postgresql.org
> Cc: pgsql-general@postgresql.org
> Subject: [ANNOUNCE] PostgreSQL 8.1.0 Release Candidate 1
>
>
> After a co
I tried but have very little progress on this. If
any one know where to find an
multi-language example (php prefered) using
postgresql, please let me know.
Thanks
[EMAIL PROTECTED]
Hello all,
I would like a generic way to generate an md5 hash for each row of a
table. Currently I do it thusly:
select id, md5(col_a || col_b || col_c || col_d) from mytable;
id | md5
--+--
1| 75acee3133f19d1a81ab2e7c1c32eb29
2|
Hello,
I implemented connection pooling in the backend of postgresql (one of the
TODO items) and I've found some really interesting performance improvements
I'd like to continue to explore but unfortunately I've also uncovered a
nasty little side effect with the memory usage. It's my guess that i
Thanks guys for your suggestions, but the problem turned out to be my
lack of experience(with PostgreSQL), combined with a bug in our PHP
Code.
Coming from a MySQL background, I assumed that if you "select x from
y", then y would be the name of a table. It turned out that in the
case that y was ac
They actually did make _some_ strides. The installer actually works
consistently (knock on veneer-covered-pressboard), which is something I
haven't seen since the pre-8i text-mode installs...
Doesn't quite compare to the 5 minute untar/config/build/install/create
database cycle we're used to with
On Tue, 1 Nov 2005, Craig wrote:
> Hi
>
> I am going to be hosting a PostgreSQL database on a new server. We will be
> purchasing a server with the AMD Athlon 64 3200+ processor.
> We are now posed with a choice of "FreeBSD 5.4" or "FreeBSD 5.4 x86_64Bit".
> My question is: Will PostgreSQL 8.0
On 2 Nov 2005 at 0:34, Tino Wildenhain wrote:
> Am Dienstag, den 01.11.2005, 16:03 -0700 schrieb Nels Lindquist:
> > Hi there.
> >
> > Sorry if this is a silly question, I'm relatively new to PostgreSQL.
> >
> > I'm trying to copy information from one column, modify it and place
> > it in anoth
Hi,
Is there any "rule of thumb" on when to (not) use clustered indexes?
What appen to the table/index? (any change on the physical organisation?)
I've seen speed improvement on some queries but I'm not sure if I must use
them or not...
My rows are imported in batch of 100 (once the main script ha
Hello,
Currently our company has a lot of small stores distributed around the country and in the actual database configuration we have a central database and all the small stores accessing it remotely.
All primary key tables were designed with a column identifying the store that it belongs. In
On Wed, Nov 02, 2005 at 05:00:35PM +0530, Venki wrote:
> >The really nasty thing about it is that because the records are now
> >considered really old, as soon as you do run VACUUM it'll start
> >removing the rows you want to save...
> So does this mean that when we do a vacuum for the first time
On 02.11.2005, at 11:47 Uhr, Martijn van Oosterhout wrote:
Ah you noticed that. Yes, many of the UTF-8 locales in FreeBSD simply
point to the ASCII versions which doesn't exactly work very well.
Right.
Only another FreeBSD system, these files are not portable. However,
the
source files for
Hi,
>The really nasty thing about it is that because the records are now
>considered really old, as soon as you do run VACUUM it'll start
>removing the rows you want to save...
So does this mean that when we do a vacuum for the first time there will still be data loss or Am I wrong in
without unique constraint even mysql replace doesnot work as expected
Jan Wieck wrote:
On 10/31/2005 11:58 AM, Lincoln Yeoh wrote:
At 08:24 AM 10/30/2005 -0800, David Fetter wrote:
>
>http://developer.postgresql.org/docs/postgres/plpgsql-control-structure
s.html#PLPGSQL-ERROR-TRAPPING
>
>
On Wed, Nov 02, 2005 at 07:40:29AM +, John Sidney-Woollett wrote:
> If you have suffered data loss for this reason, then you'll need to get
> help from the developers to see whether it can be recovered, or what you
> can do to reconstruct the data.
The really nasty thing about it is that bec
On Wed, Nov 02, 2005 at 07:27:49AM +0100, Guido Neitzer wrote:
> The LC_COLLATE for this locale is a link pointing to "../la_LN.US-
> ASCII/LC_COLLATE". This is why I don't think they paid much attention
> to the correct sort order of umlauts.
Ah you noticed that. Yes, many of the UTF-8 locales
86 matches
Mail list logo
