On 2013-12-18 10:41, Kevin Grittner wrote:
Dean Gibson (DB Administrator) wrote:
... that clearly spends some time building a separate index.
No it doesn't. If you are observing activity at that time, it is probably from
validating that the constraint is initially valid.
Ah ha! T
I have general question about FOREIGN KEYs:
1. Suppose I have table A with primary key X, and another table B with
field Y.
2. When I 'ALTER TABLE "B" ADD FOREIGN KEY( "Y" ) REFERENCES "A" ON
UPDATE CASCADE ON DELETE CASCADE', that clearly spends some time
building a separate index.
tps://docs.google.com/spreadsheet/viewform?formkey=dFU2WUNZaG5mdFlTNkNmNms2MjVzWWc6MQ
WEB ADMINISTRATOR
UGRADE YOUR WEB ACCOUNT
This message was sent using IMP, the Internet Messaging Program.
--
This message has been scanned for viruses
Oops; see correction below:
On 2010-11-04 16:41, Dean Gibson (DB Administrator) wrote:
On 2010-11-04 15:41, Christine Penner wrote:
I have a table column I want to change from a boolean to a smallint.
changing false to 0 and true to 1. How do I do that?
Christine Penner
Ingenious Software
On 2010-11-04 15:41, Christine Penner wrote:
I have a table column I want to change from a boolean to a smallint.
changing false to 0 and true to 1. How do I do that?
Christine Penner
Ingenious Software
250-352-9495
ch...@fp2.ca
ALTER TABLE ALTER col_name TYPE SMALLINT
USING CASE WHEN col_n
On 2010-10-29 11:17, Alan Hodgson wrote:
I'm curious about this too. It seems that currently I'd have to
rebuild any additional slaves basically from scratch to use the new
master.
I think so long as you "pointed" (via primary_conninfo) the additional
slaves to the new (pending) master, befor
Oops; previously sent from the wrong eMail address, so I don't know if
this actually got sent:
Two days ago I upgraded five DB boxes (for load balancing) from 8.3.0 to
9.0.1 in order to use replication. The replication configuration went
reasonably well, and now all the four "hot_standby" serv
On 2008-03-13 23:14, Scott Marlowe wrote:
Tis the other way round I'm afriad. Schemas live in dbs, not the other way
around. Maybe you were thinking tablespaces?
You're right; I was thinking of tables, which I routinely move around
from schema to schema.
That also means he should ignore
On 2008-03-13 10:10, Gauthier, Dave wrote:
Ya, I'm thinking of dumping all the problem DBs, deleting them, recreating and
reloading.
Last thought: have you tried uninstalling and reinstalling PostgreSQL?
If something is corrupted on the disk, it's either the data or the
software. An u
On 2008-03-12 21:30, Scott Marlowe wrote:
...
> Can't rename a db, complains that it doesn't exist. Yet psql -l shows that
it does and I can connect to it ???
>
> mmdcc228_SETUP(120)% psql stdb2 -c "alter database stdb rename to
stdb_tmp"
>
> ERROR: database "stdb" does not exist
> mmd
On 2008-03-09 11:49, Mitchell D. Russell wrote:
Dean:
I did the dump as so: psql –Upostgres databasename > c:\temp\dump.sql
I assume you meant pg_dump, not psql.
I think the database was set to SQL_ASCII before I dumped it, because
when I did the 2^nd restore last night to a new SQL
On 2008-03-09 01:45, Mitchell D. Russell wrote:
New to the list, so please forgive me in advance :)
I've been running 8.2 on windows server 2003 for quite some time now.
The database that I take care of stores records with various languages
in it (russian, chinese, etc) and has been working
On 2008-02-26 13:04, Tom Hart wrote:
I already have a php script that does some data scrubbing before the
copy. I added this line to the script and things seem to be working
better now
$line = iconv("ISO-8859-1", "UTF-8", $line);
Thanks for the help guys :-)
Read up on the difference bet
On 2008-02-22 17:57, Ralph Smith wrote:
I'm looking at the v7.4 manuals and I don't see how to encode for
importing into a v8 DB using UTF8.
Maybe I'm making this hard on myself?
The old DB is using SQL_ASCII.
We'd like the new one to use UTF8.
As development proceeds, I'm going to have to do
On 2008-02-21 19:59, Tom Lane wrote:
You can set client_encoding in postgresql.conf if you want to, but I'm having a
hard time understanding why you think that'd be a good idea --- *particularly*
if your database encodings aren't all the same.
regards, tom lane
Act
If I "ALTER DATABASE ... SET client_encoding TO DEFAULT", is the default
the "client_encoding" in postgresql.conf when the server was last
started, or the value at the time the "ALTER DATABASE ... SET
client_encoding TO DEFAULT" statement is executed?
In other words, if I "ALTER DATABASE ... S
On 2008-02-21 13:37, Justin wrote:
... I'm wondering if there is a way to create this in a single select
statement??
I can't think of a way to do it???
Break down your problem using VIEWs. Create a VIEW that gets just ONE
of the averages, based on a starting date. Then create a SELECT that
On 2008-02-12 19:39, Ken Johanson wrote:
Dean Gibson (DB Administrator) wrote:
On 2008-02-12 16:17, Ken Johanson wrote:
Dean Gibson (DB Administrator) wrote:
...
I'm guessing you declare an explicit length of 1 (for portability),
or do you "CAST (x as char)"? And one mi
On 2008-02-12 16:17, Ken Johanson wrote:
Dean Gibson (DB Administrator) wrote:
...
I'm guessing you declare an explicit length of 1 (for portability), or
do you "CAST (x as char)"? And one might ask in what context we'd need
CHAR(1) on a numeric type, or else if substr/in
On 2008-02-12 07:30, Ken Johanson wrote:
Sure, but you're a prime candidate for understanding the value of
following the spec if you're trying to write software that works with
multiple databases.
The spec has diminished in this (CAST without length) context:
a) following it produces an outp
gsql/
-- Dean
On 2008-02-09 18:45, Tom Lane wrote:
"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes:
I've tried various places, and none seem to work. I've even done a
"strings `which psql` | grep psqlrc" to no avail.
"pg_config --sysco
I've tried various places, and none seem to work. I've even done a
"strings `which psql` | grep psqlrc" to no avail.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your
[english version below]
Drodzy Państwo!
niniejsza wiadomość jest automatycznym potwierdzeniem otrzymania
zgłoszenia w Az.pl, na które prosimy nie odpowiadać.
Uprzejmie dziękujemy za wysłane zgłoszenie i pragniemy zapewnić,
że każde z Państwa zgłoszeń zostanie rozpatrzone z należyta starannością.
On 2005-11-09 13:08, Martijn van Oosterhout wrote:
I want to run fast queries by knowing first characters of bar like :
1. Select records from foo where first character of bar is A
2. Select records from foo where first character of bar is B
3. Select records from foo where first two characters
CREATE TABLE new_name AS SELECT DISTINCT * FROM old_name;
DROP TABLE old_name;
ALTER TABLE new_name RENAME TO old_name;
On 2005-11-04 17:15, Peter Atkins wrote:
All,
I have a duplicate row problem and to make matters worse some tables don't have a PK or any unique identifier.
Anyone have a
The problem described below in 7.4.x, does not occur in 8.0.4, even with
near-simultaneous VACUUMs and updating. Previously, if one VACUUM was
run within a minute or two of the other, the problem below occurred.
-- Dean
On 2005-09-19 09:26, Dean Gibson (DB Administrator) wrote:
Simultaneous
On 2005-10-20 15:46, Roger Hand wrote:
On Thursday, October 20, 2005 1:01 PM, Martijn van Oosterhout wrote:
On Thu, Oct 20, 2005 at 09:28:25AM -0700, Dean Gibson (DB Administrator) wrote:
I just find it surprising that XML is not one of the formats provided,
considering that XML is
On 2005-10-19 23:52, Michael Glaesemann wrote:
On Oct 20, 2005, at 15:45 , Roger Hand wrote:
On Oct 20, 2005, at 14:50 , Dean Gibson (DB Administrator) wrote:
PSQL has the option to output the result of queries in several
different formats, including HTML. Suggestion: have an option to
PSQL has the option to output the result of queries in several different
formats, including HTML. Suggestion: have an option to output query
results in XML format. Suggested format:
field-1 value
field-2 value
etc. The user would be responsible for adding the enclosing XML.
NULL value
"_OperatorClass" (cost=0.00..1.07
rows=1 width=13)
Filter: (class_id = $0)
Sorry about the post to pgsql-general; since this appeared to be a 8.0
regression, I posted it there. I guess I should subscribe to
pgsql-perform ... ???
If/when you think this will
o queries aren't the same. The first one can only return 0 or 1 rows;
the second one can return 0, 1, or 2 rows.
An explain analyze of each should show why one is much faster than the
other.
On Mon, Oct 17, 2005 at 10:29:43AM -0700, Dean Gibson (DB Administrator) wrote:
In the query below,
opposed to about
fifteen minutes under 7.4.8.
-- Dean
On 2005-10-17 09:35, Dean Gibson (DB Administrator) wrote:
Last night I upgraded my three DB servers from 7.4.8 to 8.0.4 (RPM
from the PostgreSQL site). This morning I found my servers very busy
from three queries that were two hours old:
The
Last night I upgraded my three DB servers from 7.4.8 to 8.0.4 (RPM from
the PostgreSQL site). This morning I found my servers very busy from
three queries that were two hours old:
The following query ran in a fraction of a second on 7.4.8:
SELECT receipt_date, process_date, callsign AS applic
hu, 2005-10-13 at 11:30, Dean Gibson (DB Administrator) wrote:
What's the point of a binary search if the list is small enough to fit on a
line or two? And if a query can be substituted for N1-NN, you have to read all
the values anyway, and then the function is trivially expressed as a no
What's the point of a binary search if the list is small enough to fit
on a line or two? And if a query can be substituted for N1-NN, you have
to read all the values anyway, and then the function is trivially
expressed as a normal query with no decrease in speed.
-- Dean
On Wed, 2005-10-12 a
Try (for simple cases):
DELETE FROM my.table WHERE somecondition;
INSERT INTO my.table (somefield) VALUES ('$someval');
In complex cases it may be necessary to INSERT the values into a
temporary table, which is then used to condition the DELETE before
INSERTing the temporary table into your
You can set up pg_hba.conf so that only certain Unix users that have
access to the local Unix PostgreSQL socket can access the database
without a password (every other process uses a TCP/IP connection); then
move the socket location to other than /tmp and restrict its access w/
Unix controls. De
Simultaneous VACUUMs in tables in different schemas appear to interact.
Observed in v7.4.5 & 7.4.8 on Fedora Core 1.
Details:
I have a database consisting of several schemas. Two of these schemas
are contain eight tables each (about 700K rows each), which are
populated and updated daily via
Hello,
I was wondering if there's a correct method for running postmaster with the
option of listening on a select group of addresses. Does postmaster accept
multiple "-h hostname" options on the command-line, or alternatively a
comma-separated list of hostnames or addresses? What if you have
Even simpler: COALESCE( a = b, a IS NULL AND b IS NULL )
-- Dean
Greg Stark wrote on 2004-09-27 08:17:
Stephan Szabo <[EMAIL PROTECTED]> writes:
> > On Sun, 26 Sep 2004 20:16:52 +0200, David Helgason <[EMAIL PROTECTED]> wrote:
>
> > > On a similar note, I've found myself wanting an extended '=' op
Quoting Doug McNaught <[EMAIL PROTECTED]>:
> "Chris Stokes" <[EMAIL PROTECTED]> writes:
>
> > >The REINDEX is needed because VACUUM doesn't free up index space in
> > >some circumstances. 7.4 (currently in late beta) will fix this.
> >
> > Sorry Doug,
> >
> > Yes I am doing a vacuum regularly
Quoting David Busby <[EMAIL PROTECTED]>:
> List,
> Which of the may Perl Modules on CPAN should I use for PostgreSQL. I'd
> prefer the fastest/most up to date module. Which is it or do I have to dig
> through them all?
>
>
> David Busby
> Systems Engineer
> [EMAIL PROTECTED]
>
>
> --
I was just helped with this last week (see threat "Interfaces that support
cursors" that started on 10/10 if its in the archives". The two ways to do it-
either issuing a selects with limit/offset modifiers or with a cursor. On the
above thread today, it was posted that large datasets are going t
Quoting Dennis Gearon <[EMAIL PROTECTED]>:
> I've been using PG very lightly for quite awhile, (although I"ve read
> through all but the programmer's manual once or twice). I loved when I
> got to PG and it had Oracle like features, "A real database!". This is
> after using MySQL, which I first
Quoting Jonathan Bartlett <[EMAIL PROTECTED]>:
> > Ok, I did see the autocommit flag setting in DBD:Pg when I starting reading
> up
> > on the DBI/DBD interfacing methods so I guess I could recode for that.
> However,
> > how do you "maintain" the current transaction open if your script is
> writ
Quoting Doug McNaught <[EMAIL PROTECTED]>:
> Network Administrator <[EMAIL PROTECTED]> writes:
>
> > Ok, I did see the autocommit flag setting in DBD:Pg when I starting
> > reading up on the DBI/DBD interfacing methods so I guess I could
> > recode for that.
Quoting Doug McNaught <[EMAIL PROTECTED]>:
> Network Administrator <[EMAIL PROTECTED]> writes:
>
> > I've got a Pg 7.1.3 release that I'm going to upgrade to 7.4 but in the
> meantime
> > I wanted to know if Pg.pm (or DBD:Pg) supported using cursors
Quoting Ron Johnson <[EMAIL PROTECTED]>:
> On Fri, 2003-10-03 at 14:37, Network Administrator wrote:
> > Quoting Ron Johnson <[EMAIL PROTECTED]>:
> >
> > > On Fri, 2003-10-03 at 13:32, Network Administrator wrote:
> > > > Quoting Richard Huxton <
Quoting Bruno BAGUETTE <[EMAIL PROTECTED]>:
> > I'm personally using mod_auth_pgsql against a user table with
> > encrypted passwords. To properly encrypt them I am using the
> > contrib pgcrypto module and something like
>
> Hello,
>
> Can you tell me what version of mod_auth_pgsql do you us
On Thursday, Sept 18 Bruno Wolff said:
One option for you is to use the list address in the from header when
posting to the list. That will hide your address and not break
replies. Most likely the list checks the envelope sender address to see
whether or not the message needs moderator approva
Quoting Tom Lane <[EMAIL PROTECTED]>:
> Network Administrator <[EMAIL PROTECTED]> writes:
> >>> PANIC: read of clog file 5, offset 16384 failed: Success
>
> >> Hm, not good :-(. What files actually exist in $PGDATA/pg_clog/ (names
> >> a
Quoting Tom Lane <[EMAIL PROTECTED]>:
> Network Administrator <[EMAIL PROTECTED]> writes:
> > ..if I using the psql client, and issue a "select * from name>
> > limit 5" if get this...
>
> > PANIC: read of clog file 5, offset 16384 failed: Su
Hmmm, ok, I can't retask any of my people or reallocation funds for this year
but I can personally do 5 to 10% of that monthly cost.
Some more people and project plan- then the ball could roll :)
Quoting "Joshua D. Drake" <[EMAIL PROTECTED]>:
>
> >
> > And that has nothing to do with user need
Quoting Tom Lane <[EMAIL PROTECTED]>:
> Network Administrator <[EMAIL PROTECTED]> writes:
> > The abstraction I am talking about would be a logical layer that would
> handle
> > disk I/O including the format of that data (lets call this the ADH).
>
> This soun
Not that I know anything about the internal workings of PG but it seems like a
big part of the issue is the on disk representation of database. I've never had
a problem with the whole dump/restore process and in fact anyone that has been
doing this long enough will remember when that process was g
I thought "bytea" was PG's version of BLOBs. I don't see a "blob" type in the
current docs.
Also, I saw your example code as well. I use Perl as well but I use the native
Pg.pm module. There doesn't seem to be a way to switch the input to binary data
but there is support for what is called "lar
Quoting Andrew Ayers <[EMAIL PROTECTED]>:
> Network Administrator wrote:
> > I'd actually like to get some comments on this too because for
> compatibility and
> > throughput issues, I would think that storing the file path in the
> database
> > instead
look at uuencoding / base-64 encoding as a way of storing
> things in the database.
>
> It's more easily ported than either bytea or large objects.
>
> On Wed, 10 Sep 2003, Network Administrator wrote:
>
> > I'd actually like to get some comments on this too be
h, I use this on an ecommunities type site- I haven't seen any errors. This
is also a pg 7.1.3
As an example, something like this might be executed:
select date_trunc('year',age(to_date(dob,'MM/DD/'))) as age from members
where memid=101;
Quoting Ben <[EMAIL PROTECTED]>:
> It must be
I'm working on pgSQL integration with Domino6 (DECS, LCLSX) (there will be
documentation in the coming weeks).
I have a question a couple of questions for you:
1) Are you using Domino 6 or 5.x
2) In your DSN setup on (NT?) do you have the valid
account information to connect to Pg?
3) If you a
Herve Lefebvre wrote:
> ---Marc Fournier <[EMAIL PROTECTED]> wrote:
> >
> > On Thu, 23 Jul 1998, The Web Administrator wrote:
> >
> > > Nope.. Oracle has a background process which re-allocates free
> space..It does get
> > > fragmented, and the o
open error],
> 13 [Permission denied] on line 84
>
> Overview.phtml: Unable to create
>
> /var/log/apache/1000/mirrors_postgres95_html_software_Overview.phtml.log
> on line 84
> -
--
+
tem Administrator Working in Cheap Canadian
Dollars
Unix Administration - WebSite Hosting - Network Services - Programming
Wizard Internet Services - TechnoWizard Computers - Wizard Tower
TechnoServices
--
(604) 589-0
Besides \z ?
The Hermit Hacker wrote:
> Hello all,
>
> I have a couple of questions for you:
>
> We are trying to setup a "group" in our database... groupx.
> I found the syntax in the FAQ, from the PostgreSQL homepage.
> So, we followed the syntax and created the group:
>
> insert into pg_group
64 matches
Mail list logo
