On Wed, Nov 24, 2004 at 08:55:35AM +0100, Alberto Cabello Sanchez wrote:
On Sun, Nov 21, 2004 at 05:50:30PM +, Chris Green wrote:
Has anyone got any recommendations for forms/reports generators for
postgresql? I can probably get the forms I want from Rekall so the
bigger requirement is
Hello,
First, let me apologize for my flurry of emails as of late... I'm working
on something which seems particularly difficult (at least to me)...
My question is regarding creating an index on a view, or perhaps another
way to accomplish this. For example:
CREATE TABLE table1 (
table1_id
Krause, Lewis wrote:
I know there is a command to add the column. I want to back up the table
before I alter it. What is the best way. It has a couple indexes and a
trigger.
pg_dump -t table-name ...
--
Richard Huxton
Archonet Ltd
---(end of
Mark Dexter wrote:
We use a development environment that works with Postgres via ODBC and
uses cursors to insert and update rows in Postgres tables. I'm using
Postgres version 7.4.5.
A. If I TRUNCATE or DELETE all of the rows in the table and then run
VACUUM or ANALYZE on the empty table, the
On Tue, Nov 23, 2004 at 09:07:04PM -0500, Gary L. Burnore wrote:
We've removed all of the comp.databases.postgres.* groups from our server
and our feeds anyway. Do did google. So will anyone else who's still
holding the bogus groups.
Basically, the thing that Marc is doing that's 'bad',
Is there a to get the row creation time if we know
it's object ID ??
Thx
Net Virtual Mailing Lists wrote:
My question is regarding creating an index on a view, or perhaps
another way to accomplish this.
Views are just macro expansions of queries (in a manner of speaking).
To make queries on views use indexes, you create the indexes on the
underlying tables in the
Hunter Hillegas wrote:
I need to import a file into a Unicode database.
I am getting an error:
ERROR: Unicode characters greater than or equal to 0x1 are not
supported
CONTEXT: COPY mailing_list_entry, line 30928, column
first_last_name: Ver?nica
If your file really does have
Some time ago, an excellent tutorial on materialized views with PostgreSQL
was pointed at this address
http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html
Patrick
--
-
Patrick Fiche
On Nov 24, 2004, at 7:07 PM, Najib Abi Fadel wrote:
Is there a to get the row creation time if we know it's object ID ??
Only if you have a timestamp column on the table that records the
creation time. For example,
create table foo (
foo_id serial not null unique
,
Actually i have an old table without any timestamp column. I want to know
the creation time of one Row: One way is to look in the postgres dump files
(which could take some time) in order to see the date the Row was inserted.
I was hoping there is another way
Thx anyways.
- Original
Maybe a bit outside the scope of PGSQL but when designing webapps where different users
can edit the same record at the same time, what is the best way to solve this situation?
One way is to use an update field, that gets selected with rest of the data and when
updating the data, check this
I create a primary key on the oid column. The oid is generate by system
itself. The oid may overlap, when this happen, this operation may fail?
---(end of broadcast)---
TIP 6: Have you searched our list archives?
On Nov 24, 2004, at 8:18 PM, songsubo wrote:
I create a primary key on the oid column. The oid is generate by
system itself. The oid may overlap, when this happen, this operation
may fail?
Yes. Check the mailing list archives, as this was just recently
discussed.
Michael Glaesemann
grzm
The problem in my case is that the view does a join between table2 and
table3 and I want to do a select on a value from table2. So at the point
the expansion happens, I am actually doing a query on a column that does
not exist in table3 - it only exists in table2.
Given what you said, perhaps a
songsubo wrote:
I create a primary key on the oid column. The oid is generate by
system itself. The oid may overlap, when this happen, this operation
may fail?
Yes. If you want a self-incrementing primary key, why not use a SERIAL type?
--
Richard Huxton
Archonet Ltd
If I want to create a postgres database on multiple machines, is the
practice of tarring or zipping up binaries compiled on one machine and
untarring them on another, and using the binaries (initdb etc) acceptable?.
This removes the need for having a compiler and environment on the target
machine,
Hi!
I am trying to get postgres to use my index but it just doesn't. Please
Help!
It follows the table definition and a series of commands I thought would
ensure usage of my index.
CREATE TABLE data.question_result (
id bigserial PRIMARY KEY,
trial_idbigint
Ulrich Meis wrote:
CREATE TABLE data.question_result (
id bigserial PRIMARY KEY,
trial_idbigint NOT NULL REFERENCES data.trial(id),
question_id bigint REFERENCES content.question(id),
two more columns,
);
mydb=# explain analyze select *
Net Virtual Mailing Lists wrote:
CREATE TABLE table2 (
table2_id INTEGER,
table2_desc VARCHAR,
table3_id INTEGER[]
);
CREATE TABLE table3 (
table3_id INTEGER,
table3_desc VARCHAR
);
What I need is an indirect index (for lack of a better phrase) that
allows me to do:
SELECT b.table3_id,
Francis Reed wrote:
If I want to create a postgres database on multiple machines, is the
practice of tarring or zipping up binaries compiled on one machine and
untarring them on another, and using the binaries (initdb etc) acceptable?.
This removes the need for having a compiler and environment on
Sure, this is what Linux distributers do. They compile postgresql into
a binary package which is installed on the user's machine.
It works as long as the environments are reasonably compatable, all
have readline, similar libc, etc.
Hope this helps,
On Wed, Nov 24, 2004 at 12:30:28PM -,
That's what we had hoped.
We tested the principle with postgres 7.4.6 but found a what we believe is a
compile time dependancy in create_conversion.sql where $libdir is not being
resolved properly during the initdb process on the second machine. The usual
environment variables don't seem to help
Francis Reed [EMAIL PROTECTED] writes:
If I want to create a postgres database on multiple machines, is the
practice of tarring or zipping up binaries compiled on one machine and
untarring them on another, and using the binaries (initdb etc) acceptable?.
This removes the need for having a
Title: Performance
Hello,
Ich have a big performance problem.
I'm running postgres 7.4.2 on Suse Linux 9.0 on a dual Xeon 3.0 GHz with 3 Gbyte Ram.
In postgres.conf I'm using the defaults. Filesystem is ext3 with writeback journaling
I have 3 tables with ca 10 million entries with a gist
last one I hope
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: [EMAIL PROTECTED]
Francis Reed wrote:
Hi Richard, Sun/Sparc is the platform of choice in our case. We tested the
principle with postgres 7.4.6 but found a what we believe is a compile time
dependancy in create_conversion.sql where $libdir is not being resolved
properly during the intidb process. The usual
Hi,
I am newbie in postgresql.I want to modify column datatype of a table
how can i do it.
for example there is a column with datatype varchar, i want to change it
to text.
How can i.
Thanks
Regards
Durga
---(end of broadcast)---
TIP 6: Have
Werdin Jens wrote:
Hello,
Ich have a big performance problem.
I'm running postgres 7.4.2 on Suse Linux 9.0 on a dual Xeon 3.0 GHz with 3
Gbyte Ram.
In postgres.conf I'm using the defaults.
That's the place to start. See the guide at:
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
Francis Reed wrote:
We tested the principle with postgres 7.4.6 but found a what we
believe is a compile time dependancy in create_conversion.sql where
$libdir is not being resolved properly during the initdb process on
the second machine. The usual environment variables don't seem to
help
Durga Prasad Mohapatra wrote:
I am newbie in postgresql.I want to modify column datatype of a
table how can i do it.
for example there is a column with datatype varchar, i want to change
it to text.
How can i.
Before 8.0: Recreate the table and copy the data.
After 8.0: ALTER TABLE
--
I recently tried to upgrade from the 7.2.1 PostGreSQL package on Debian Stable
to the 7.4.6 PostGreSQL package on Debian Testing. The automatic update
failed, message included below. The documentation for manual upgrades
references a script which does not appear to exist (postgresql-dump) in the
Eric D Nielsen wrote:
I recently tried to upgrade from the 7.2.1 PostGreSQL package on Debian Stable
to the 7.4.6 PostGreSQL package on Debian Testing. The automatic update
failed, message included below. The documentation for manual upgrades
references a script which does not appear to exist
Peter Eisentraut wrote:
Francis Reed wrote:
We tested the principle with postgres 7.4.6 but found a what we
believe is a compile time dependancy in create_conversion.sql where
$libdir is not being resolved properly during the initdb process on
the second machine. The usual environment variables
Net Virtual Mailing Lists [EMAIL PROTECTED] writes:
When I do a pgdump --schema=someschema somedatabase something.dump,
the results of the dump file look like this:
CREATE TABLE emailtemplates (
email_template_id integer DEFAULT
nextval('emailtemplate_email_templat_seq'::text) NOT NULL,
Eric D Nielsen wrote:
I recently tried to upgrade from the 7.2.1 PostGreSQL package on
Debian Stable to the 7.4.6 PostGreSQL package on Debian Testing. The
automatic update failed, message included below. The documentation
for manual upgrades references a script which does not appear to
Joshua D. Drake wrote:
Well you can't just upgrade 7.2.1 to 7.4.6. You have to dump and
restore.
The Debian package does that automatically. On some days...
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(end of broadcast)---
Is it possible for one stored procedure to call another? I would like
certain procs to call a different proc to update an audit trail
whenever they're executed.
I thought about using triggers but want the trail to include info
that's not in the updated table - specifically the application user
Peter,
Thanks for the reply.
Perhaps I should go into some more detail about what is going on.
Originally, the database was in SQL_ASCII and the data had been imported via
COPY from a text file. The text file is no longer available. The data went
into the table just fine.
When selecting from
Yes, it's no different than any other select statement. Just keep in
mind that it's all one big happy transaction - if the inner stored proc
aborts, so does the outer one. (Actually, that may have changed in
version 8, but I don't know.)
On Nov 24, 2004, at 8:17 AM, Larry White wrote:
ne
Peter Eisentraut wrote:
Joshua D. Drake wrote:
Well you can't just upgrade 7.2.1 to 7.4.6. You have to dump and
restore.
The Debian package does that automatically. On some days...
Really? WOW! I wonder if Gentoo does that. That is pretty
remarkable.
Sincerely,
Joshua D. Drake
--
Hi,
the docu about the Read Committed Transaction Isolation Level pointed out: ... The point at issue
here is whether or not within a single command we see an absolutely consistent view of the
database..
Because i dont want to use the Serializable Transaction Isolation Level or table locks if
Ben wrote:
Yes, it's no different than any other select statement. Just keep in
mind that it's all one big happy transaction - if the inner stored
proc aborts, so does the outer one. (Actually, that may have changed
in version 8, but I don't know.)
In 8 you could use an exception in plPgsql I
On Wed, Nov 24, 2004 at 11:17:26AM -0500, Larry White wrote:
Is it possible for one stored procedure to call another? I would like
certain procs to call a different proc to update an audit trail
whenever they're executed.
What happened when you tried it? If you're having trouble then it
Hi,
Am Mittwoch, den 24.11.2004, 11:17 -0500 schrieb Larry White:
Is it possible for one stored procedure to call another?
yes.
More specifically these are stored functions rather then
stored procedures in the M$-like way.
Regards
Tino
---(end of
On Wed, 2004-11-24 at 08:30 -0800, Joshua D. Drake wrote:
Peter Eisentraut wrote:
Joshua D. Drake wrote:
Well you can't just upgrade 7.2.1 to 7.4.6. You have to dump and
restore.
The Debian package does that automatically. On some days...
Really? WOW! I wonder if
Francis Reed [EMAIL PROTECTED] writes:
We tested the principle with postgres 7.4.6 but found a what we believe is a
compile time dependancy in create_conversion.sql where $libdir is not being
resolved properly during the initdb process on the second machine.
We only started supporting the idea
Thanks very much for the information. It would appear that our best option
might be to vacuum analyze these tables in our application at a point in time
when they contain rows instead of doing it at night. Needlesst to say, it
would nice to have an option to analyze with a target number of
Thanks for the quick responses yesterday. At a minimum, it seems like
this behavior does not match what is described in the Postgres
documentation (more detail below). But I still have a hard time
understanding the results of these two queries:
select SUBSTRING(
On 2004-11-23, Marc G. Fournier [EMAIL PROTECTED] wrote:
On Wed, 24 Nov 2004, Peter Eisentraut wrote:
Marc G. Fournier wrote:
Due to recent action by Google concerning the
comp.databases.postgresql.* hierarchy, we are going to make some
changes that should satisfy just about everyone ... over
Quoting Peter Eisentraut [EMAIL PROTECTED]:
Eric D Nielsen wrote:
I recently tried to upgrade from the 7.2.1 PostGreSQL package on
Debian Stable to the 7.4.6 PostGreSQL package on Debian Testing. The
automatic update failed, message included below. The documentation
for manual upgrades
Eric D Nielsen [EMAIL PROTECTED] writes:
There were two sets of errors. One set dealing with FATAL 1: unsupported
frontend protocol during the data dumping stage of the automatic update
script. It appears that the data was successfully dumped, however. Should I
be worried? Is this FATAL
On 2004-11-24, Marc G Fournier From : [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] (Andrew - Supernews) writes:
Any chance of there being regular (or even only occasional) signed
checkgroups messages for the new hierarchy?
Sure, but I've never done it before, so if you can help ... ?
Sure. You
Hi,
Does anyone know of an open source single master- multi (or single) slave
replication system for Postgresql 7 or 8 which handles BLOBs?
Thanks,
Mark Childerson
Mark Childerson
espressoCode inc.
Ph: 416-963-8793
Fax: 416-963-8643
www.exdocs.com
www.espressocode.com
From: Mark Dexter [EMAIL PROTECTED]
Thanks very much for the information. It would appear that our best
option might be to vacuum analyze these tables in our
application at a point in time when they contain rows instead of doing it
at night. Needlesst to say, it would nice to have an option
Hi
have a bit of a issue im planning on using tableoid to select the
appropate table, but im not sure that you can in sql select statement?
If not is there another approch i could be using?
--
*Jamie Deppeler
*Database Administrator
---(end of
Ken Tanzer [EMAIL PROTECTED] writes:
Thanks for the quick responses yesterday. At a minimum, it seems like
this behavior does not match what is described in the Postgres
documentation (more detail below).
After looking at this more, I think that it is actually behaving as
Spencer designed
Is there a SQL query to retrieve the start time of a) when the postmaster
was started, and/or b) when the stats were last reset? I'd like to
calculate a few rates over time...
TIA.
Ed
---(end of broadcast)---
TIP 3: if posting/reading through
Actually this database has been carried forward since the postgres95
days, so you are definitely right in your analysis..
Would another (perhaps safer?) way of doing this is to remove the CREATE
SEQUENCE and SELECT pg_catalog.setval, and replace the DEFAULT
nextval with SERIAL then restore
pgpool(http://www2b.biglobe.ne.jp/~caco/pgpool/index-e.html) surely
can handle large objects. I don't know any other replication solutions
can handle them.
Be carefull, however, please make sure that the master and the slave
database clusters are physically synced before starting
replication.
Tatsuo Ishii wrote:
pgpool(http://www2b.biglobe.ne.jp/~caco/pgpool/index-e.html) surely
can handle large objects. I don't know any other replication solutions
can handle them.
Mammoth Replicator can, but it is not open source.
Sincerely,
Joshua D. Drake
Be carefull, however, please make sure
Alberto Cabello Sanchez wrote:
On Sun, Nov 21, 2004 at 05:50:30PM +, Chris Green wrote:
I'm after a good report generator for a postgresql database. I am
using Rekall at the moment which is quite good but I'm not quite able
to do some of the things I want.
Has anyone got any
I have big_table (1M rows) and small_table (1K rows) with
identical schemas and together in a view as follows:
create view big_view as
select *, 'big_table'::varchar as source from big_table
union
select *, 'small_table'::varchar as source
I just noticed PostgreSQL's schemas for my first time.
(http://www.postgresql.org/docs/current/static/ddl-schemas.html)
I Googled around, but couldn't find any articles describing WHY or
WHEN to use schemas in database design.
Since the manual says HOW, could anyone here who has used schemas
Is it possible for one stored procedure to call another? I would like
a number of procs to call another proc to update an audit trail
whenever they're called.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL
[EMAIL PROTECTED] (Gary L. Burnore) writes:
It appears that his aliiasing hasn't actually taken effect yet. Once it
does, apparently things will be slightly better because he's then sending
posts to pgsql.* not comp.databases.postgres.* .As of a short while
ago, we were still receiving
Hi,
the docu about the Read Committed Transaction Isolation Level pointed
out: ... The point at issue here is whether or not within a single
command we see an absolutely consistent view of the database..
Because i dont want to use the Serializable Transaction Isolation
Level or table locks if it
I have a query surrounding somthing taht seems to have been a relatively
FAQ. It concerns the use of temporary tables in plpgsql.
Which initially resulted in the good old 'oid not found' error.
So I learnt from the maliing-list that I should be 'executing' (with EXECUTE
Command) my queries
Ed L. [EMAIL PROTECTED] writes:
create view big_view as
select *, 'big_table'::varchar as source from big_table
union
select *, 'small_table'::varchar as source from small_table;
Try UNION ALL instead of just union
The difference is that
Hi ALL,
Ive been using postgres for 3 years and now we are having problems with
its
performance.
Here are some givens..
We have 260 subscription tables per Database.
We have 2 databases.
Our main client has
Dear JM ,
Ive been using postgres for 3 years and now we are having problems
with its
PostgrSQL version please
--
With Best Regards,
Vishal Kashyap.
Lead Software Developer,
http://saihertz.com,
http://vishalkashyap.tk
---(end of
PG Version 7.3.4
On Thursday 25 November 2004 14:12, Vishal Kashyap @ [SaiHertz] wrote:
Dear JM ,
Ive been using postgres for 3 years and now we are having
problems with its
PostgrSQL version please
---(end of broadcast)---
TIP 9:
SQL:
update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where mobile_num in (select
mobile_num from LOADED_MOBILE_NUMBERS)
Could you try using UPDATE ... FROM (SELECT ) AS .. style syntax?
About 20 minutes ago, I changed a 8 minute update to an most instant by
doing that.
regards
Iain
On Thu, 25 Nov 2004 14:00:32 +0800, JM [EMAIL PROTECTED] wrote:
update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where mobile_num in
(select
mobile_num from LOADED_MOBILE_NUMBERS)
does loaded_mobile_numbers have a primary key or index on mobile_num?
same for subscriptiontable?
have
update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where mobile_num in (select
mobile_num from LOADED_MOBILE_NUMBERS)
Change to:
update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where exists (select 1 from
LOADED_MOBILE_NUMBERS lmn where
lmn.mobile_num=SUBSCRIPTIONTABLE.mobile_num);
That should run a
75 matches
Mail list logo
