All,I have a large PG 9.1.1 server and replica using log shipping. I had some hardware issues on the replica system and now I am getting the following in my pg_log/* files. Same 2 lines over and over since yesterday.2011-12-01 07:46:30 EST LOG: restored log file "0001028E00E5" from
I use the one in /contrib/start-scripts from the src tarball.
-- Original Message ---
From: [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Sent: Fri, 28 Apr 2006 10:53:25 -0400 (EDT)
Subject: [GENERAL] Init script for 8.1 compiled on Debian Stable
Hi all,
For the first
I would like to bring up again that it would be nice if vacuum/analyze recorded
the last action and stats in a table
(pg_vacuum) within each database. Then dba's can make better decisions how
vacuum/analyze is working as well as
tuning the fsm guc's. This should be a real easy additions to
why not a join like below (not tested)
select id
from p4_versions a
join p4_files b on (a.versionof = b.id and a.version = b.headver)
where p4path like '%/date.txt'
-- Original Message ---
From: Mark Harrison [EMAIL PROTECTED]
To: Postgresql-General
why don't you just (not tested)
insert into report (col1,col2,col3) SELECT col1,col2,col3 FROM t2 order by id
This should get the row into report in id order, you need to put in the correct
column names
-- Original Message ---
From: Andrus [EMAIL PROTECTED]
To:
why not just
update pg_database set datname='foodmart' where datname like 'foodmart%';
-- Original Message ---
From: Ari Kahn [EMAIL PROTECTED]
To: Tom Lane [EMAIL PROTECTED]
Cc: Stephen Frost [EMAIL PROTECTED], pgsql-general@postgresql.org
Sent: Tue, 25 Apr 2006 11:08:09
an genius, but ... :-)
Anyway, that worked and is the solution!
postgres=# update pg_database set datname='foodmart' where datname
like 'foodmart%';
UPDATE 1
postgres=# drop database foodmart;
DROP DATABASE
THANKS!
On Apr 25, 2006, at 11:13 AM, Jim Buttafuoco wrote:
why not just
then this will not work.
-- Original Message ---
From: Jim C. Nasby [EMAIL PROTECTED]
To: Jim Buttafuoco [EMAIL PROTECTED]
Cc: Alexander Scholz [EMAIL PROTECTED], pgsql-general@postgresql.org
Sent: Fri, 21 Apr 2006 00:51:17 -0500
Subject: Re: [GENERAL] Query to check existence
nice, this could be put into a plpgsql function with error handling.
-- Original Message ---
From: Jim C. Nasby [EMAIL PROTECTED]
To: Alexander Scholz [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org
Sent: Fri, 21 Apr 2006 00:54:51 -0500
Subject: Re: [GENERAL] Query to check
Give this function a try, examples at the end, I used Postgresql 8.1.3
-- s is the schema to look in
-- f is the function name
create or replace function isfunctionavailable(s text,f text)
returns bool
as
$$
declare
ans bool;
begin
select into ans true
from pg_proc p
join pg_namespace n
don't forget about schema's, you will need to join with pg_namespace.oid and
pg_proc.pronamespace
-- Original Message ---
From: A. Kretschmer [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Sent: Tue, 18 Apr 2006 16:14:25 +0200
Subject: Re: [GENERAL] Query to check existence
create a view with insert/update/delete rules and DON'T let the users assign to
the serial col. remove
insert/upload/delete permission to the base table and only allow access via the
view.
-- Original Message ---
From: Steven Brown [EMAIL PROTECTED]
To: Csaba Nagy [EMAIL
try activestate perl, I have used it a number of times on windows XP/2000 for
CLI stuff (data loading).
-- Original Message ---
From: Roy Souther [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Sent: Wed, 01 Mar 2006 16:00:21 -0700
Subject: [GENERAL] Need a GNU SQL CLI tool
if its linux, use iptables to block to port.
-- Original Message ---
From: Karl Wright [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Sent: Thu, 23 Feb 2006 15:49:09 -0500
Subject: [GENERAL] How do I prevent binding to TCP/IP port outside of localhost?
I have a situation
just go to the src/pl/plperl directory and do a make make install, then
the createlang command
-- Original Message ---
From: Craig [EMAIL PROTECTED]
To: Andreas Kretschmer [EMAIL PROTECTED], pgsql-general@postgresql.org
Sent: Sat, 11 Feb 2006 14:45:51 +0200
Subject: Re:
I would be nice if vacuum/analyze recorded in a table pg_vacuum (for example)
the last time it ran and some stats for
each table
Jim
-- Original Message ---
From: Karl O. Pinc [EMAIL PROTECTED]
To: Tom Lane [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org
Sent: Thu, 09 Feb
why not have server a start at 1 and server b start at max int8/2. that will
give each server alot of orders before
you have an issue. They will will not have to play games with the sequence
value.
Jim
-- Original Message ---
From: Philippe Ferreira [EMAIL PROTECTED]
To:
use pg_dump --schema-only
-- Original Message ---
From: Rich Shepard [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Sent: Sat, 28 Jan 2006 10:14:05 -0800 (PST)
Subject: [GENERAL] Viewing Database Scheme
I'm trying to help the XRMS developers add postgres support ('cause
looks like perl/DBD::Pg is looking for libpq.so.3 but postgresql 8.1.X supplies
libpq.so.4. You need to rebuild DBD::Pg
to get this to work (atleast that is what I have been doing for my upgrades
from 7.4.X to 8.1.2)
Jim
-- Original Message ---
From: Rich Shepard [EMAIL
you need to use EXECUTE to do the dynamic lock table.
sql = 'LOCK TABLE ' || sequence_name || 'IN ACCESS EXCLUSIVE MODE';
EXECUTE sql;
-- Original Message ---
From: Philippe Ferreira [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Sent: Wed, 25 Jan 2006 18:37:21 +0100
why not just use setval(), see docs for arguments.
-- Original Message ---
From: Philippe Ferreira [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Sent: Wed, 25 Jan 2006 22:11:11 +0100
Subject: Re: [GENERAL] My very first PL/pgSQL procedure...
Hi,
The only solution I've
Change the column type in the view to text, then in the insert/update rule, if
the value is '' insert null or what ever,
else insert the date (as text) into the real date column (as a date)
Jim
-- Original Message ---
From: Martin Pohl [EMAIL PROTECTED]
To: Doug McNaught
take a look at dblink in the contrib directory, it has a function called
dblink_current_query() that returns the current
query. I use it all the time.
Jim
-- Original Message ---
From: rlee0001 [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Sent: 11 Jan 2006 14:57:42
John
check to see if the objects are in the template1 database which just gets
copied 100% to the target db.
Jim
-- Original Message ---
From: John Wells [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Sent: Thu, 5 Jan 2006 14:09:02 -0500 (EST)
Subject: [GENERAL] Drop
try something like this
jim=# create table a (a text,b text, c text);
CREATE TABLE
jim=# create unique index a_idx on a(a,b,(coalesce(c,'*** NULL IS HERE ***')));
CREATE INDEX
jim=# insert into a values ('a','b','c');
INSERT 413272154 1
jim=# insert into a values ('a','b',null);
INSERT
you could also use a big number to if the column is a int/int8/float/numeric.
Jim
-- Original Message ---
From: Jim Buttafuoco [EMAIL PROTECTED]
To: Bruno Wolff III [EMAIL PROTECTED], CSN [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org pgsql-general@postgresql.org
Sent: Fri
you should use the 8.1 pg_dump not the 7.3 one.
Give it a try
-- Original Message ---
From: Benjamin Smith [EMAIL PROTECTED]
To: Postgres General pgsql-general@postgresql.org
Sent: Mon, 28 Nov 2005 16:22:18 -0800
Subject: [GENERAL] Errors upgrading from 7.3 to 8.1
Currently
you were close, try the following (untesting). You need the alias on the outer
my_table for this to work
select year report_year,
sum(amount),
sum(select amount from my_table where year = a.year)
from my_table a
group by report_year;
-- Original Message
give this a try if you don't mind using plperlu. This was tested with
Postgresql 8.0.3
create or replace function gethostbyaddr(inet) returns text
as
$$
use strict;
use Socket;
my $inet = $_[0];
my $iaddr=inet_aton($inet);
my $name = gethostbyaddr($iaddr,AF_INET);
return $name;
$$
language
try something like:
select x,sum(case when id5 then 1 else 0 end),sum(case when id10 then 1 else
0 end from test2 group by x;
-- Original Message ---
From: peter pilsl [EMAIL PROTECTED]
To: Martín Marqués martin@bugs.unl.edu.ar, PostgreSQL List
pgsql-general@postgresql.org
Does anyone have a source (free if possible) of data to get Route miles
between 2 US zipcodes. I am currently using
the earthdistance contrib code but it does a straight line which doesn't work
well if you are trying to figure out the
cost of driving from 03101 to 11530
Thanks
Jim
Terry,
How easy is it to get the route miles from one zip to another using with
PCMiler or Rand McNally. Is it just a query on
some route table ie: select miles from route where zip1 = '1' and zip2 =
'2'. Also do either of these cover Canada?
Thanks
Jim
-- Original Message
John,
I changed your function like follows (Postgresql V8.03)
drop function generate_table_count ();
drop type rowcounts_t;
create TYPE rowcounts_t as (name TEXT, count int);
create or replace function generate_table_count () returns setof
rowcounts_t as
$$
declare
tname record;
c rowcounts_t;
I believe the correct way is the following.
1. get the oid from pg_class for relname='pg_toast_100194'
2. lookup up the relname from pg_class where reltoastrelid = the oid from the
first query.
so on my system
select oid from pg_class where relname='pg_toast_17070';
oid
---
17072
(1
how about (untested)
select *
from
(
SELECT DISTINCT ON (linkid) *
FROM all_links
WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day',TIMESTAMP
'2005-06-01')
ORDER BY linkid
) A
ORDER BY read_date DESC limit 100
-- Original Message ---
From: FC [EMAIL
try the following (untested) query:
SELECT la.name,lb.name pp.distance FROM payway_profile AS pp
JOIN location AS la ON ( pp.location_a = l.location_id )
join location AS lb ON ( pp.location_b = l.location_id );
-- Original Message ---
From: Frodo Larik [EMAIL PROTECTED]
To:
I did say untested :)
-- Original Message ---
From: Michael Fuhr [EMAIL PROTECTED]
To: Jim Buttafuoco [EMAIL PROTECTED]
Cc: Frodo Larik [EMAIL PROTECTED], pgsql-general@postgresql.org
Sent: Fri, 25 Mar 2005 09:05:50 -0700
Subject: Re: [GENERAL] Stuck with references
On Fri, Mar
try select * from ONLY people.
also check out this query
select relname,people.* from people join pg_class on
people.tableoid=pg_class.oid;
and
select relname,people.* from ONLY people join pg_class on
people.tableoid=pg_class.oid;
Jim
-- Original Message ---
From: Scott
Hi all,
I am having a problem with the pg_views view. It seems that the
internal function pg_get_viewdef() is the problem. See below.
This problem is preventing me from dumping the database (either as 1
export or 1 table at a time). I have recreated the database, but the
problem seems to
I am currently working on a patch to 7.2 that will allow data/indexes to
be in different locations. I am also looking at replacing the current
LOCATION code with a table driven (pg_locations) that will allow not
only the default data/indexes locations to be set but also each
table/index to have
Mike
I use the following PLPERL/select code to view all FK's in my database
. I guess the select could be made into a pg_fkeys view. What do
people think...
Just a note. I used PLPERL because the fkey data is stored in a BYTEA
data field and other then a C function PLPERL works fine for
41 matches
Mail list logo
