Re: [GENERAL] Monitoring Tool for PostgreSQL

[Basques, Bob (CI-StPaul)]

We use a PERL script to handle this sort of thing.  It’s nice this way since we 
can run them from just about anywhere.

bobb


On Oct 18, 2017, at 12:37 PM, Fabricio Pedroso Jorge 
> wrote:

Hi all,

   is there a "official" monitoring tool for PostgreSQL databases? For example, 
i come from Oracle Database, and there, we have Enterprise Manager to monitor 
and administrer the product... is there such a similar tool for PostgreSQL?

Thanks for the attention.

--
Fabrício Pedroso Jorge.

Administrador de Banco de Dados

Resumo Profissional:
http://br.linkedin.com/in/fabriciojorge

Contatos:
+ 55 91 988991116
skype: fabricio.pedroso.jorge
fpjb...@gmail.com


"Get your facts first, then you can distort them as you please. “
- Mark Twain

Re: [GENERAL] Problems with the time in data type timestamp without time zone

[Basques, Bob (CI-StPaul)]

Where is the machine running the database physically located, in another 
timezone possibly?

bobb


On Oct 18, 2017, at 10:33 AM, David G. Johnston 
> wrote:

On Wed, Oct 18, 2017 at 8:21 AM, américo bravo astroña 
> wrote:
Hi,

I have a program that saves information in a DB Postgresql need to extract data 
from date and time of that DB but when I retrieve the date and time information 
is always ahead 3 hours, the type of data that has that field is timestamp 
without time zone,

Please forgive my english I'm using translator.

​A minimal SQL example of your problem would help.

David J.​



Don’t ever wrestle with a pig. You’ll both get dirty, but the pig will enjoy it.
—Cale Yarborough

Re: [GENERAL] Issue with json_agg() and ordering

[Bob Jones]

>>
>> I guess that the order by should be in the aggregation.
>>
>> SELECT json_agg(a.* ORDER BY a.last_name, a.last_year DESC)
>> FROM my_table a;
>
> yes.  however, you would say, json_agg(a... not 'a.*').  The .*
> notation only works in certain contexts, and is transformed at parse
> time to, a.col1, a.col2, a.col3...  which would not work inside an
> aggregation function which can only handle a single column or record.
>
> merlin


Awesome ! Thanks Charles for the answer, and Merlin for the
tweaking/additional insight.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Issue with json_agg() and ordering

[Bob Jones]

Hi,


Could anyone give me a few pointers as to how I might resolve the following :

select json_agg(my_table) from (my_table) where foo='test' and bar='f'
order by last_name asc, first_name asc;

ERROR:  column "my_table.last_name" must appear in the GROUP BY clause
or be used in an aggregate function
LINE 1: ...foo='f' order by last_name ...


I suspect It doesn't really matter what my table looks like for the
purposes of the above, but if you need something to go by:

create table my_table(
last_name text,
first name text,
foo text,
bar boolean
);

Thanks !

Bob


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres processes getting stuck (bug?)

[Bob Lunney]

Check the wait state for the backend process.  I’ve seen this happen when a 
process ran a per-row trigger that tried to connect to something else, and it 
filled the ip_conntrack table.  So, kernel level wait along with a whole bunch 
of locks on the table in question.  

Running pg_terminate_backend() didn’t work, as the signal queued behind the 
kernel wait.  We had to bounce the database to get rid of the problem.  
Immediately afterwards we disabled the trigger.

HTH,

Bob Lunney

> On Apr 29, 2016, at 1:30 PM, Ciprian Grigoras <ciprian.grigo...@vitals.com> 
> wrote:
> 
> Hi guys,
> 
> I had a question. We're running Postgresql 9.0.7 , and all of a sudden we 
> started seeing unexpected behavior. One table got locked after we ran one 
> simple INSERT on one test item.
> Nothing else was running against that table as far as we know, and the query 
> is frozen there now. After some time we tried to terminate it forcefully 
> (pg_terminate_backend), ran that and the return of the command is "true" but 
> the query still stays on, shows up on pg_stat_activity etc.
> Reading from the table is fine, but we believe any other inserts / updates 
> are not possible.
> 
> Restarting the Postgresql server is not an option at this moment, since it's 
> a production box and another high-demand database is running from that.
> 
> Has anyone seen a similar issue (maybe a bug in the 9.0 version ?) where 
> simple statements don't finish and get locked there and can't be killed by 
> the pg_terminate_backed ? What is the cause of this ?
> Any reasonable way to find out more details on what caused this, how to 
> prevent it in the future, and how it can be fixed sensitively now ? Thanks !
> 
> just fyi, checking the data in the "pg_locks" (for that process that is 
> frozen), shows a bunch of rows all with the same virtualtransactionid. Only 
> one of them has the mode of "ExclusiveLock" (the only record there with a 
> locktype of "virtualxid"), a few have the "RowExclusiveLock" mode and the 
> vast majority have the "AccessShareLock" mode.
> 
> Thanks,
> Ciprian

Re: [GENERAL] Initdb --data-checksums by default

[Bob Lunney]

> On Apr 22, 2016, at 3:21 AM, Stuart Bishop <stu...@stuartbishop.net> wrote:
> 
> On 20 April 2016 at 14:43, Alex Ignatov <a.igna...@postgrespro.ru> wrote:
>> Hello everyone!
>> Today in Big Data epoch silent data corruption becoming more and more issue
>> to afraid of. With uncorrectable read error rate ~ 10^-15   on multiterabyte
>> disk bit rot is the real issue.
>> I think that today checksumming data  must be mandatory  set by default.
>> Only if someone doesn't care about his data he can manually turn this option
>> off.
>> 
>> What do you think about defaulting --data-checksums in initdb?
> 
> I think --data-checksums should default to on.
> 
> Databases created 'thoughtlessly' should have safe defaults. Operators
> creating databases with care can elect to disable it if they are
> redundant in their environment, if they cannot afford the overhead, or
> consider their data low value enough to not want to pay the overheads.
> 
> If the performance impact is deemed unacceptable, perhaps the ability
> to turn them off on an existing database is easily doable (a one way
> operation).
> 
> -- 
> Stuart Bishop <stu...@stuartbishop.net>
> http://www.stuartbishop.net/
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

+1

Bob Lunney
Lead Data Architect
MeetMe, Inc.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgresql 9.3.4 file system compatibility

[Bob Lunney]

XFS absolutely does.  Its well supported on Redhat and CentOS 6.x and 7.x.  
Highly recommended.

Don’t know about OCFS2.

Bob Lunney
Lead Data Architect
MeetMe, Inc.

> On Apr 8, 2016, at 8:56 AM, Marllius <marll...@gmail.com> wrote:
> 
> Hi guys!
> 
> The OCFS2 and XFS have compatibility with postgresql 9.3.4? 
> 
> I was looking the documentation but i not found it.
> 
> 
> 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Processing data from table using awk.

[Basques, Bob (CI-StPaul)]

Just to throw in an idea.

I almost exclusively use PERL for this type of thing.  A bunch of examples out 
on the web using DBI, and the main aspects are portable across many databases, 
not just POSTGRES.

Just my two cents.

AWK would work too, I’ve used it myself, and got very complicated with it as 
well, but you’ll eventually end up looking for more capabilities, and start 
pulling in other commands like SED, etc.  Perl just keeps on working.

bobb



> On Oct 6, 2015, at 9:25 AM, Reid Thompson  wrote:
> 
> On Tue, 2015-10-06 at 09:04 -0500, John McKown wrote:
> 
>> I'm wanting to do some reporting on data which I have an a PostgreSQL table.
>> For lack of anything better, I've decided to see if I can do it in GNU awk.
> 
> 
> perhaps... note the 4th extension...
> 
> https://www.gnu.org/software/gawk/manual/html_node/gawkextlib.html
> As of this writing, there are seven extensions:
> 
> errno extension
> GD graphics library extension
> PDF extension
> PostgreSQL extension
> MPFR library extension (this provides access to a number of MPFR 
> functions that gawk’s native MPFR support does not)
> Redis extension
> XML parser extension, using the Expat XML parsing librar
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PG 9.4.4 issue on French Windows 32 bits

[Bob Lunney]

Thierry,

Please post the output of 

$ psql -l

for the database in question for both the 32-bit and 64-bit servers. That will 
show what encoding was specified when the databases where created.

Also, post the output of 

$ psql dbname here login -c “show client_encoding”
$ psql dbname here login -c show all | grep lc_ 

s'il vous plaît.  Also check if any code is setting client_encoding or any of 
the lc_* options on the fly.

Bob Lunney
Senior Database Engineer
AWeber Communications, LLC
1100 Manor Drive
Chalfont, PA  18914 USA




 On Jul 8, 2015, at 12:48 PM, Daniel Verite dan...@manitou-mail.org wrote:
 
   Thierry Hauchard wrote:
 
 When restoring from backup (created from 8.4 database with PG_Dump
 9.4.4), the log shows errors about UTF like :
 2015-07-07 17:03:35 CEST ERREUR:  séquence d'octets invalide pour
 l'encodage « UTF8 » : 0xf4 0x6c 0x65 0x20
 
 [...]
 
 UPDATE test_table SET str_field = '\\' WHERE id = 75160909
 - ERROR:  invalid byte sequence for encoding UTF8: 0xee 0x6e 0x65
 
 
 These sequences of bytes seem to come from LATIN1-encoded
 error messages from the backend, translated to french.
 
 0xf4 0x6c 0x65 is ôle which could come from rôle=role in
 english, a fragment of message that occurs routinely when
 restoring a dump granting permissions to roles that don't exist in
 the target cluster.
 
 0xee 0x6e 0x65 is îne as in chaîne which is string in french. It's
 plausible that the above update, given standard_conforming_strings
 to false, produces the translated version of:
  nonstandard use of \\' in a string literal
 which is:
  utilisation non standard de \\' dans une chaîne littérale
 where non-surprisingly, the first non US-ASCII sequence is îne
 
 See how lc_messages is configured in postgresql.conf.
 Presumably it's French_France.1252 ?
 
 If you can live with english messages, set it to C, otherwise
 someone more knowledgeable in Windows might suggest a
 proper explanation and fix.
 Personally I don't understand in the first place how UTF-8
 is handled with  '*.1252' locales, as cp1252 seems
 incompatible with UTF-8 by definition.
 
 
 Best regards,
 -- 
 Daniel
 PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
 
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to retrieve Comment text using SQL, not psql?

[Bob Futrelle]

Using pgAdmin3 I've tried this and variations on it.   All are rejected.

select COMMENT ON TABLE articlestats


No answer here,

http://www.postgresql.org/docs/9.3/static/sql-comment.html


pgAdmin3 had no problem with entering a comment:

COMMENT ON TABLE articlestats IS 'Comprehensive data for every article.'


 - Bob Futrelle

Re: [GENERAL] Re: Stuck trying to backup large database - best practice? How about a cloud service?

[Bob Futrelle]

Many worthwhile things cost money.
I never suggested you wouldn't have to pay.

- Bob

On Tue, Jan 13, 2015 at 12:27 AM, Michael Nolan htf...@gmail.com wrote:



 On Mon, Jan 12, 2015 at 7:46 PM, Bob Futrelle bob.futre...@gmail.com
 wrote:

 You should be able to find a cloud provider that could give you many TB.
 Or so they like to claim.


 Nope, but you probably find one willing to SELL you access to many TB.
 --
 Mike Nolan

[GENERAL] Re: Stuck trying to backup large database - best practice? How about a cloud service?

[Bob Futrelle]

You should be able to find a cloud provider that could give you many TB.
Or so they like to claim.

 - Bob

Re: [GENERAL] Very high latency, low bandwidth replication

[Bob Jolliffe]

Thanks Francisco for these inputs.  I hadn't considered log shipping as I
knew I didn't want to track changes to all tables (and databases).  Setting
up a local partial mirror is an interesting thought which hadn't crossed my
mind .. I'll giver that some consideration.

Though currently I am thinking to address the problem of generating deltas
at the application level rather than to use postgresql features which are
largely optimized for a slightly different set of circumstances and
requirements.

Impressive what can be done witha 2400 baud modem when you set your mind to
it.  Fortunately this days are mostly behind us :-)


On 30 June 2014 13:05, Francisco Olarte fola...@peoplecall.com wrote:

 Hi Bob.

 On Mon, Jun 30, 2014 at 10:05 AM, Bob Jolliffe bobjolli...@gmail.com
 wrote:
  What are people's thoughts about a more optimal solution?  I would like
 to
  use a more incremental approach to replication.  This does not have to
 be a
  live replication .. asynchronously triggering once every 24 hours is
  sufficient.  Also there are only a subset of tables which are required
 (the
  rest consist of data which is generated).


 If you only need to replicate once every 24 hours, which means you can
 tolerate lags, you could try log shipping. Instead of sending the wal
 records from master to standby directly just spool them, compress them
 as much as you can ( I would try pglesslog plus an XZ on it's output
 ), and send it once a day. This for the 'incremental part'. For the
 only a subset of tables, you could try to set up a local partial
 mirror using any of the trigger based replication products and then do
 log-shipping of that.

 Also, the logical replication slot stuff added to the latest version
 seems really promissing for this kind of thing, but I'm not familiar
 enough with it to recommend anything.

 Also, depending on your data updating patterns, database sizes and
 other stuff, a trigger based replication approach can save a lot of
 traffic. I mean, if you have records which are heavily updated, but
 only replicate once a day, you can collapse all the day stuff in a
 single update. I once did a similar thing to transmit deltas over a
 2400bps modem by making daily sorted dumps and sending daily deltas
 with previous day ( it needed a bit of coding, about a couple hundred
 lines, but produced ridiculously small deltas, and with a bit of care
 their application was idempotent, which simplified the recovery on
 errors ).

Francisco Olarte.

Re: [GENERAL] Very high latency, low bandwidth replication

[Bob Jolliffe]

Thanks Stuart.  I'll do some measurements on plaintext dump to git.


On 2 July 2014 09:46, Stuart Bishop stu...@stuartbishop.net wrote:

 On 30 June 2014 15:05, Bob Jolliffe bobjolli...@gmail.com wrote:

  What are people's thoughts about a more optimal solution?  I would like
 to
  use a more incremental approach to replication.  This does not have to
 be a
  live replication .. asynchronously triggering once every 24 hours is
  sufficient.  Also there are only a subset of tables which are required
 (the
  rest consist of data which is generated).

 WAL shipping is probably best here. Configure an archive_command on
 the master to  compress and push logs to cloud storage, and configure
 a hot standby on site to pull and decompress the logs. The wal-e tool
 may make things simpler pushing to cloud storage, or just follow the
 PostgreSQL documentation to archive the WAL files to a filesystem.

 If that isn't good enough, you can look at more esoteric approaches
 (eg. nightly plaintext dumps to a git repository, pushing changes to
 disk on site).


 --
 Stuart Bishop stu...@stuartbishop.net
 http://www.stuartbishop.net/

[GENERAL] Very high latency, low bandwidth replication

[Bob Jolliffe]

Hi

I have been grappling with a problem for some time I would appreciate some
advice on.  We have a public health application which is web based with a
postgresql backing store which is designed for use by the public sector
ministry of health in a significant number of African, Asian and other
countries (http//:dhis2.org).  Traditionally it is hosted as a national
data warehouse application with users dispersed amongst district offices
and sometimes health facilities around the country.

Particularly in many countries in Africa the public sector typically has
limited data centre infrastructure to reliably host the application
in-house and so a good number have opted to use some global cloud service
(infrastructure as a service) to ensure maximum availability of the
application.  Others have managed to make use of in-country resources such
as national ISPs and mobile companies.  There are many cost-benefit and
governance considerations behind these decisions which I don't need to go
into here.

Whereas ministries have been prepared to do this there are important to
reasons to ensure that a backup of the database can be maintained in the
ministry.  So we attempt to grab the nightly snapshot backups from the
database each night.  In the past I have attempted this somewhat
simplistically with rsync over ssh but it is a very inefficient approach
and particularly so over weak internet connections.

What are people's thoughts about a more optimal solution?  I would like to
use a more incremental approach to replication.  This does not have to be a
live replication .. asynchronously triggering once every 24 hours is
sufficient.  Also there are only a subset of tables which are required (the
rest consist of data which is generated).

Appreciate any advice.

Regards
Bob

[GENERAL] Re: PostgreSQL 9.3 XML parser seems not to recognize the DOCTYPE element in XML files

[Bob Moyers]

I have this table:
CREATE TABLE REPORT_STYLE (
REPORT_STYLE_NAME VARCHAR(75) NOT NULL,
REPORT_STYLE_VERSION NUMERIC(8,0) NOT NULL,
UPDATE_TS TIMESTAMP NOT NULL,
UPDATE_USER_NAME VARCHAR(30) NOT NULL,
STYLE_DESCR VARCHAR(200),
JASPER_STYLE XML
);
ALTER TABLE REPORT_STYLE ADD CONSTRAINT PK_RPTSTY PRIMARY KEY 
(REPORT_STYLE_NAME);


And this document (notice the !DOCTYPE):

?xml version=1.0 encoding=UTF-8?
!DOCTYPE jasperTemplate PUBLIC -//JasperReports//DTD Template//EN 
http://jasperreports.sourceforge.net/dtds/jaspertemplate.dtd;

jasperTemplate
   style name=LabelPlainSmall forecolor=#00 backcolor=#FF 
fontSize=7 isBold=false isItalic=false isUnderline=false 
isStrikeThrough=false/
   style name=TextBoxDefault forecolor=#00 backcolor=#FF 
isBlankWhenNull=true fontSize=9/
   style name=ScheduleTitle forecolor=#00 backcolor=#FF 
fontSize=14 isBold=true/
   style name=CheckBox forecolor=#00 backcolor=#FF 
hAlign=Center vAlign=Middle isBlankWhenNull=true fontSize=8
 box
topPen lineWidth=1.0 lineStyle=Solid 
lineColor=#00/
leftPen lineWidth=1.0 lineStyle=Solid 
lineColor=#00/
bottomPen lineWidth=1.0 lineStyle=Solid 
lineColor=#00/
rightPen lineWidth=1.0 lineStyle=Solid 
lineColor=#00/
 /box
   /style
   style name=LabelBoldSmall forecolor=#00 backcolor=#FF 
fontSize=7 isBold=true/
   style name=ScheduleName forecolor=#00 backcolor=#FF 
fontSize=10 isBold=true/
   style name=LabelDefault forecolor=#00 backcolor=#FF 
fontSize=8 isBold=false isItalic=false isUnderline=false 
isStrikeThrough=false/
   style name=LabelBoldMedium forecolor=#00 backcolor=#FF 
fontSize=8 isBold=true/
   style name=LabelPlainLarge forecolor=#00 backcolor=#FF 
fontSize=10 isBold=false isItalic=false isUnderline=false 
isStrikeThrough=false/
   style name=LabelBoldLarge forecolor=#00 backcolor=#FF 
fontSize=10 isBold=true/
   style name=NumberDefault forecolor=#00 backcolor=#FF 
fontSize=9 isBold=true/
   style name=LabelPlainExtraSmall forecolor=#00 
backcolor=#FF fontSize=6 isBold=false/
   style name=LabelBoldExtraSmall forecolor=#00 
backcolor=#FF fontSize=6 isBold=true/
   style name=Header forecolor=#00 backcolor=#FF 
fontSize=9/
   style name=Footer forecolor=#00 backcolor=#FF 
fontSize=9/
   style name=CoverSheetName forecolor=#00 backcolor=#FF 
isBlankWhenNull=true fontSize=12 isBold=true/
   style name=SmallCheckBox forecolor=#00 backcolor=#FF
 box
topPen lineWidth=1.0/
leftPen lineWidth=1.0/
bottomPen lineWidth=1.0/
rightPen lineWidth=1.0/
 /box
   /style
/jasperTemplate



When I try this update:
UPDATE REPORT_STYLE SET JASPER_STYLE = XMLPARSE(DOCUMENT ?) WHERE 
(REPORT_STYLE_NAME = ?)

I get:
org.postgresql.util.PSQLException: ERROR: invalid XML content
Detail: line 2: StartTag: invalid element name
!DOCTYPE jasperTemplate PUBLIC -//JasperReports//DTD Template//EN http://jas
^


I AM using 'XMLPARSE(DOCUMENT'.   Why the error?

Using PG 9.3 with this JDBC driver:
10:37:51,822 INFO  [org.jboss.as.connector.deployers.jdbc] (MSC service thread 
1-11) JBAS010404: Deploying non-JDBC-compliant driver class 
org.postgresql.Driver (version 9.2)

[GENERAL] Installed postgres.app 9.3.1.0. pgadmin doesn't appear to see it

[Bob Futrelle]

I uninstalled 9.2 before installing 9.3.1.0.

The app is called Postgres93, it is version 9.3.1.0
I downloaded the latest pgAdmin, it is pgAdmin3 version 1.18.1

I have a database MiniServer which is supposed to use postgres
as its Maintenance database.
But there is no such database.

Perhaps it can't find the DBs that were there?

pg_upgrade requires

pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir [option...]

but I'm not sure what the 'old' values should be (since I uninstalled 9.2 -
maybe I shouldn't have?)

In my /Library/PostgreSQL/9.2/data/global I see 42 files fom 8K to 25K in
size, most created
last March - my data must be there.  So that may be telling me to create an
empty folder,
 /Library/PostgreSQL/9.3/

In my Postgres93 bundle there's a Contents/MacOS/postgres, a small unix
executable.
Is that the binary?   But again there's no binary for 9.2.

I was rolling along with 9.2 getting lots of good work done via
Eclipse/JDBC.
But now I'm stuck.

Sorry for being so dense. But I've never been afraid to ask questions.
The answers I get increase my understanding of whatever the topic is.

Thanks in advance for helping a tyro.

 - Bob

[GENERAL] pg_hba.conf broken after cluster upgrade

[Bob Jolliffe]

Hello

I just upgraded my postgresql server from 8.4 to 9.2 on ubuntu linux 12.04.
 I installed the new version then used pg_upgrade to upgrade and replicate
the cluster from the old server to the new.

Everything appears to have worked well except that I am left with a problem
with my pg_hba.conf file.  For some strange reason it only accepts a single
line.  So by default I had just:

local all postgres ident

All attempts to add any additional lines to this file, such as

 host all all 127.0.0.1/32 md5

 lead to the following error on startup:

2013-10-16 16:43:41 GMT LOG:  authentication option not in name=value
format: local
2013-10-16 16:43:41 GMT CONTEXT:  line 1 of configuration file
/etc/postgresql/9.2/main/pg_hba.conf
2013-10-16 16:43:41 GMT FATAL:  could not load pg_hba.conf

Note that either the host line or the local line on their own are fine.
 But any attempt to have more than one line (in any order) leads to this
error.

Any idea what on earth can I have done and how can I fix it?

Bob

Re: [GENERAL] I need more specific instructions for switching to digest mode for this list

[Bob Futrelle]

Me bad. It was right there in front me.

I'm now in Digest Mode.

 - Bob



On Wed, Oct 9, 2013 at 12:00 PM, Igor Neyman iney...@perceptron.com wrote:

  -Original Message-
  From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
  ow...@postgresql.org] On Behalf Of Raymond O'Donnell
  Sent: Wednesday, October 09, 2013 11:54 AM
  To: Bob Futrelle
  Cc: pgsql-general@postgresql.org
  Subject: Re: [GENERAL] I need more specific instructions for switching to
  digest mode for this list
 
  On 09/10/2013 16:47, Bob Futrelle wrote:
   Trying to switch to the digest didn't work.
   How do I find more specific details about switching?
  
  
   On Wed, Oct 9, 2013 at 12:27 AM, Bob Futrelle bob.futre...@gmail.com
   mailto:bob.futre...@gmail.com wrote:
  
 set pgsql-general digest
  
  --
  Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
  To make changes to your subscription:
  http://www.postgresql.org/mailpref/pgsql-general

 It says it all right at the bottom:

  To make changes to your subscription:
  http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] I need more specific instructions for switching to digest mode for this list

[Bob Futrelle]

Trying to switch to the digest didn't work.
How do I find more specific details about switching?


On Wed, Oct 9, 2013 at 12:27 AM, Bob Futrelle bob.futre...@gmail.comwrote:

   set pgsql-general digest

[GENERAL]

[Bob Futrelle]

  set pgsql-general digest

[GENERAL] How to restore some DBs to a new server?

[Bob Futrelle]

Running on my MacBook Pro, pgAdmin3 tells me I have four servers in Server
Groups,
all local, no network involved.

I have dumped two of my DBs from a current server using pg_dump.
Then in pgAdmin3 I created another server, let's call it New.

I would like to look at these earlier DBs while not disturbing my current
DBs.
So I would like to reload both the dumps into New,
but I can't find the magic invocation that does this,
in pgAdmin3 or using pg_restore or directly in psql.

I realize that this isn't rocket science,  so I'll blush a bit when the
simplicity of it is revealed.

   - Bob

[GENERAL] Cleaning up a text import

[Bob Pawley]

Hi

I imported some text using the Quantum GIS dxf2postgiswhich somehow 
became distorted through the import.


What should have been imported was TK-208.

What I got was %%UTK-208%%U.

Perhaps I did something wrong while using dxf2postgis?

Otherwise, I can trim the text using - select trim (both '% U' from 
'%%UTK-208%%U') .


However I would need to know what it is that needs to be trimmed from 
future imports, which isn't always possible.


I would appreciate any suggestions on how to resolve this.

Many thanks in advance.

Bob

[GENERAL] pgAdmin shows two servers with the identical data

[Bob Futrelle]

I have two PG servers with the same data.

I know the data is the same, because if I change a value in a table

on one server, it changes the value in a table with the same

name in the other server.

in pgAdmin III:


Properties for server Local (localhost:5432):

Name: Local

Host: localhost

Port: 5432

Maintenance DB: postgres

UserName: robertfutrelle

Properties for server Local(local:.s.PGSQL.55432)

Name: Local

Host:

Port: 5432

Maintenance DB: postgres

UserName: robertfutrelle


The second one is odd - no Host is listed.


What is going on?


Since they appear to be identical in most all respects, would be nice

to remove one of them. Is that safe? If so, which?


All the above in Mac Mountain Lion OS. All my PG work is being done

entirely locally, on my MacBook. I intend to work in that mode for at least

the next two years.


My son suggested I move to PostgreSQL. I started with it late last year

and have never looked back. PG is definitely one cool DB system.

Thanks guys.


My data is 120,000 biology papers, HTML, and a great deal of secondary data

I generate from them.


- Bob Futrelle

[GENERAL] JDBC - Need to declare variables for values in insert statement

[Bob Futrelle]

Do the declare statements and insert all have to be done in one statement
execute()?
That is, what is the scope of variables I declare?
I see a variety of syntax examples, some for older versions?
I'm using pg 9.2.2, so what are the rules/syntax for declaring and using
variables?

Use case: I collect metadata from XML articles such as title, journalName,
volume, year.  For each article I need to create a pg table record and
insert the values for the various metadata items in the article.

This is my first post to this list.

Thanks,

 - Bob

Re: [GENERAL] JDBC - Need to declare variables for values in insert statement

[Bob Futrelle]

Here's a small, but complete code example - Bob

package us.tsos.dbs.pg;

import java.sql.*;

/**
 * This is an effort to get a computed value from a Java function
 * (or data object) included properly in the VALUES entries.
 * So, how can I declare an SQL variable and set its value to some Java
value?
 * Results viewed in pgAdmin3  with query 'select * from public.hello'.
 *
 * Jar in classpath is postgresql-9.2-1002.jdbc4.jar
 *
 * @version 0.1  Mon Jan 28 EST 2013
 * @author Bob Futrelle
 */

public class JDBCVariableTest {

Connection db;
Statement st;
Boolean boo;

public static void main(String[] args) throws SQLException {

JDBCVariableTest testIt = new JDBCVariableTest();
testIt.helloVariables();
}

public int f1() { return 3;}

public void helloVariables() throws SQLException  {

int intVar = f1();  // OK in Java, but SQL/JDBC?

try {
db = DriverManager.getConnection(jdbc:postgresql:Articles,
robertfutrelle, my pw);
st = db.createStatement();
boo  = st.execute(CREATE TABLE IF NOT EXISTS hello ( Name VARCHAR NOT NULL
PRIMARY KEY, value int));

// Declare   ??
 // INSTEAD OF THE LITERAL 4 VALUE (which works)
// how do I declare a variable and assign the f1() return value to it
// and then include it so the value 3 appears in the inserted record?
 //st.execute(insert into hello values('aKey',4));
st.execute(insert into hello values('bKey',4));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}



On Mon, Jan 28, 2013 at 8:39 AM, Dave Cramer p...@fastcrypt.com wrote:

 Bob,

 Can you provide a snippet of code so I can understand what you mean by
 declare ?


 Dave Cramer

 dave.cramer(at)credativ(dot)ca
 http://www.credativ.ca


 On Mon, Jan 28, 2013 at 7:11 AM, Bob Futrelle bob.futre...@gmail.comwrote:

 Do the declare statements and insert all have to be done in one statement
 execute()?
 That is, what is the scope of variables I declare?
 I see a variety of syntax examples, some for older versions?
 I'm using pg 9.2.2, so what are the rules/syntax for declaring and using
 variables?

 Use case: I collect metadata from XML articles such as title,
 journalName, volume, year.  For each article I need to create a pg table
 record and insert the values for the various metadata items in the article.

 This is my first post to this list.

 Thanks,

  - Bob

Re: [GENERAL] database design best pratice help

[Bob Futrelle]

Yes. The general rules are:

Many normalized tables.  OK.
Denormalizing simply to reduce the number of  tables.  Not OK.

 - Bob


On Mon, Jan 28, 2013 at 1:47 PM, Kevin Grittner kgri...@ymail.com wrote:

 Jose Soares jose.soa...@sferacarta.com wrote:

  In my db I have about one hundred tables like this:
 
  code
  description
 
  To avoid to have a so great number of similar tables in the db
  I wonder if it is a good idea to unify all these tables in one
  big table like this:
 
  id
  code
  table_ name
  description

  Could this be a way to enhance db performance?

 No.  It could easily hurt performance, and will create a mess of
 your data.

  Is there any negative point that I don't see?

 For starters you can't use foreign keys to ensure data integrity.

 I would not only leave the separate tables, but I would create a
 separate domain for the code column of each, to be used everywhere
 a code is of that nature.

 I recommend reading up on concepts for normalizing a relational
 database.  If you mix different logical types of data in one
 column, you don't even meet the requirements of first normal form,
 and reduce the ability to cleanly apply relational concepts to your
 data.

 -Kevin



 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC - Need to declare variables for values in insert statement

[Bob Futrelle]

Thanks to Edson and Dave for lightning responses.
I'm confident that your answers will do the job.
I'll follow up on the advice AFTER I get my coffee ;-)

I'm really focused on the NLP content of my research,
but I need a DB infrastructure to do it right.
(Not a bunch of files as in ancient times.)

 --- Bob



On Mon, Jan 28, 2013 at 1:59 PM, Edson Richter edsonrich...@hotmail.comwrote:

  You have two options:

 st.execute(insert into hello values ('bKey', +f1()+));

 or

 PreparedStatement st = db.prepareStatement(insert into hello values
 ('bKey', ?));
 st.setInteger(1, f1());


 where 1 is the first parameter, 2 is the second parameter, and so on.


 Regards,

 Edson Richter



 Em 28/01/2013 16:50, Bob Futrelle escreveu:

 Here's a small, but complete code example - Bob

  package us.tsos.dbs.pg;

  import java.sql.*;

  /**
  * This is an effort to get a computed value from a Java function
  * (or data object) included properly in the VALUES entries.
  * So, how can I declare an SQL variable and set its value to some Java
 value?
  * Results viewed in pgAdmin3  with query 'select * from public.hello'.
  *
  * Jar in classpath is postgresql-9.2-1002.jdbc4.jar
  *
  * @version 0.1  Mon Jan 28 EST 2013
  * @author Bob Futrelle
  */

  public class JDBCVariableTest {

  Connection db;
  Statement st;
  Boolean boo;

  public static void main(String[] args) throws SQLException {

  JDBCVariableTest testIt = new JDBCVariableTest();
  testIt.helloVariables();
  }

  public int f1() { return 3;}

  public void helloVariables() throws SQLException  {

  int intVar = f1();  // OK in Java, but SQL/JDBC?

  try {
  db = DriverManager.getConnection(jdbc:postgresql:Articles,
 robertfutrelle, my pw);
  st = db.createStatement();
  boo  = st.execute(CREATE TABLE IF NOT EXISTS hello ( Name VARCHAR NOT
 NULL PRIMARY KEY, value int));

  // Declare   ??
   // INSTEAD OF THE LITERAL 4 VALUE (which works)
  // how do I declare a variable and assign the f1() return value to it
  // and then include it so the value 3 appears in the inserted record?
   //st.execute(insert into hello values('aKey',4));
  st.execute(insert into hello values('bKey',4));
  } catch (SQLException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
  }
  }
 }



 On Mon, Jan 28, 2013 at 8:39 AM, Dave Cramer p...@fastcrypt.com wrote:

 Bob,

  Can you provide a snippet of code so I can understand what you mean by
 declare ?


 Dave Cramer

 dave.cramer(at)credativ(dot)ca
 http://www.credativ.ca


 On Mon, Jan 28, 2013 at 7:11 AM, Bob Futrelle bob.futre...@gmail.comwrote:

 Do the declare statements and insert all have to be done in one
 statement execute()?
 That is, what is the scope of variables I declare?
 I see a variety of syntax examples, some for older versions?
 I'm using pg 9.2.2, so what are the rules/syntax for declaring and using
 variables?

  Use case: I collect metadata from XML articles such as title,
 journalName, volume, year.  For each article I need to create a pg table
 record and insert the values for the various metadata items in the article.

  This is my first post to this list.

  Thanks,

   - Bob

Re: [GENERAL] JDBC - Need to declare variables for values in insert statement

[Bob Futrelle]

I had read 'through' the JDBC material,
but now reading more deeply with more insight.
The API is useful too.

Anyhoo, PreparedStatement works like a charm, viz.,

PreparedStatement pstmt= db.prepareStatement(insert into hello values
('cKey', ?));
pstmt.setInt(1,intVar);
pstmt.execute();


This also worked, include the function directly, skip the java field.  Nice.

pstmt.setInt(1,f1());


Makes sense, because the setInt() API says that the value resides in the
Java world.

Sets the designated parameter to the given Java int value.


I have about 250 source files on my machine that use PreparedStatements.
I wrote a few of them, but usually by grabbing code from the web.
The rest are demos or parts of systems I used for some of my work.
Used embedded Derby = JavaDB back then - history now.
The fact that the code contained a few PreparedStatements was not what I
focused on.
The code just worked, doing what I needed.

My new understanding now allows me to do a ton of good NLP stuff.

Back to the future.

Thanks all,

 - Bob


On Mon, Jan 28, 2013 at 2:22 PM, Edson Richter edsonrich...@hotmail.comwrote:

  I would recommend the reading of the excellent The Java Tutorial, that
 has a very well explained section about JDBC:
 http://docs.oracle.com/javase/tutorial/jdbc/index.html


 and the chapter about PreparedStatements:
 http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html


 Regards,

 Edson Richter


 Em 28/01/2013 17:09, Bob Futrelle escreveu:

 Thanks to Edson and Dave for lightning responses.
 I'm confident that your answers will do the job.
 I'll follow up on the advice AFTER I get my coffee ;-)

   I'm really focused on the NLP content of my research,
 but I need a DB infrastructure to do it right.
 (Not a bunch of files as in ancient times.)

   --- Bob



 On Mon, Jan 28, 2013 at 1:59 PM, Edson Richter 
 edsonrich...@hotmail.comwrote:

  You have two options:

 st.execute(insert into hello values ('bKey', +f1()+));

 or

 PreparedStatement st = db.prepareStatement(insert into hello values
 ('bKey', ?));
 st.setInteger(1, f1());


 where 1 is the first parameter, 2 is the second parameter, and so on.


 Regards,

 Edson Richter



 Em 28/01/2013 16:50, Bob Futrelle escreveu:

 Here's a small, but complete code example - Bob

  package us.tsos.dbs.pg;

  import java.sql.*;

  /**
  * This is an effort to get a computed value from a Java function
  * (or data object) included properly in the VALUES entries.
  * So, how can I declare an SQL variable and set its value to some Java
 value?
  * Results viewed in pgAdmin3  with query 'select * from public.hello'.
  *
  * Jar in classpath is postgresql-9.2-1002.jdbc4.jar
  *
  * @version 0.1  Mon Jan 28 EST 2013
  * @author Bob Futrelle
  */

  public class JDBCVariableTest {

  Connection db;
  Statement st;
  Boolean boo;

  public static void main(String[] args) throws SQLException {

  JDBCVariableTest testIt = new JDBCVariableTest();
  testIt.helloVariables();
  }

  public int f1() { return 3;}

  public void helloVariables() throws SQLException  {

  int intVar = f1();  // OK in Java, but SQL/JDBC?

  try {
  db = DriverManager.getConnection(jdbc:postgresql:Articles,
 robertfutrelle, my pw);
  st = db.createStatement();
  boo  = st.execute(CREATE TABLE IF NOT EXISTS hello ( Name VARCHAR NOT
 NULL PRIMARY KEY, value int));

  // Declare   ??
   // INSTEAD OF THE LITERAL 4 VALUE (which works)
  // how do I declare a variable and assign the f1() return value to it
  // and then include it so the value 3 appears in the inserted record?
   //st.execute(insert into hello values('aKey',4));
  st.execute(insert into hello values('bKey',4));
  } catch (SQLException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
  }
  }
 }



 On Mon, Jan 28, 2013 at 8:39 AM, Dave Cramer p...@fastcrypt.com wrote:

 Bob,

  Can you provide a snippet of code so I can understand what you mean by
 declare ?


 Dave Cramer

 dave.cramer(at)credativ(dot)ca
 http://www.credativ.ca


 On Mon, Jan 28, 2013 at 7:11 AM, Bob Futrelle bob.futre...@gmail.comwrote:

 Do the declare statements and insert all have to be done in one
 statement execute()?
 That is, what is the scope of variables I declare?
 I see a variety of syntax examples, some for older versions?
 I'm using pg 9.2.2, so what are the rules/syntax for declaring and
 using variables?

  Use case: I collect metadata from XML articles such as title,
 journalName, volume, year.  For each article I need to create a pg table
 record and insert the values for the various metadata items in the article.

  This is my first post to this list.

  Thanks,

   - Bob

[GENERAL] Server to run Postgresql

[Bob Pawley]

Hi

I have an unused computer which I am considering turning into a server to run 
my Postgresql database.

Is this even possible to do?

If so, can someone suggest an open source server that is relatively easy to set 
up? Windows based would be ideal.

Bob

[GENERAL] Using Insert with case

[Bob Pawley]

Hi

select
case when somevariable = 2
then (insert into pipe (line)
select bob.edge_data.edge_id
from bob.edge_data, bob.node, pipe
where st_intersects(st_startpoint(bob.edge_data.geom), bob.node.geom)
and bob.node.node_id = 415
and pipe.id = 1)

I am attempting to use the above. However, with or without the enclosing 
brackets I get a syntax error on the word into.

Help will be appreciated.

Bob

Re: [GENERAL] Using Insert with case

[Bob Pawley]

Hi Alban

Probably no difference except I have four cases and I was trying, in an 
attempt to save processing time, to compact commands a little.


Bob

-Original Message- 
From: Alban Hertroys

Sent: Tuesday, August 07, 2012 3:40 PM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Using Insert with case

On 8 Aug 2012, at 24:26, Bob Pawley wrote:


Hi

select
case when somevariable = 2
then (insert into pipe (line)
select bob.edge_data.edge_id
from bob.edge_data, bob.node, pipe
where st_intersects(st_startpoint(bob.edge_data.geom), 
bob.node.geom)

and bob.node.node_id = 415
and pipe.id = 1)

I am attempting to use the above. However, with or without the enclosing 
brackets I get a syntax error on the word into.


Is that somehow different from this?

insert into pipe (line)
   select bob.edge_data.edge_id
   from bob.edge_data, bob.node, pipe
   where st_intersects(st_startpoint(bob.edge_data.geom), 
bob.node.geom)

   and bob.node.node_id = 415
   and pipe.id = 1
and somevariable = 2


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest. 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Trouble with NEW

[Bob Pawley]

The function is too long to copy.

I separated it into another trigger function with just the update statement.

Here is the error -

ERROR: record new has no field fluid_id
SQL state: 42703
Context: SQL statement update p_id.fluids
   set fluid_short =
   (select shape.text
   from  shape, num_search
   where (select st_within(shape.wkb_geometry, 
st_geometryn(num_search.the_geom4, 1)) = 'true')

   and text !~ '[0-9]')
-
Here is the trigger function -

CREATE OR REPLACE FUNCTION fluid_name()
 RETURNS trigger AS $$

   begin
   update p_id.fluids
   set fluid_short =
   (select shape.text
   from  shape, num_search
   where (select st_within(shape.wkb_geometry, 
st_geometryn(num_search.the_geom4, 1)) = 'true')

   and text !~ '[0-9]')
   where p_id.fluids.fluid_id = NEW.fluid_id;


 RETURN NULL;
   End;

$$  LANGUAGE plpgsql;

   create trigger r_fluidname after insert on project.project
   for each row execute procedure fluid_name();


Here is the trigger script -

-- Trigger: r_fluidname on project.project

-- DROP TRIGGER r_fluidname ON project.project;

CREATE TRIGGER r_fluidname
 AFTER INSERT
 ON project.project
 FOR EACH ROW
 EXECUTE PROCEDURE public.fluid_name();


Here is the function script -

-- Function: public.fluid_name()

-- DROP FUNCTION public.fluid_name();

CREATE OR REPLACE FUNCTION public.fluid_name()
 RETURNS trigger AS
$BODY$

   begin
   update p_id.fluids
   set fluid_short =
   (select shape.text
   from  shape, num_search
   where (select st_within(shape.wkb_geometry, 
st_geometryn(num_search.the_geom4, 1)) = 'true')

   and text !~ '[0-9]')
   where p_id.fluids.fluid_id = NEW.fluid_id;


 RETURN NULL;
   End;

$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;
ALTER FUNCTION public.fluid_name()
 OWNER TO postgres;


Here is the triggering table -

-- Table: project.project

-- DROP TABLE project.project;

CREATE TABLE project.project
(
 p_id_id serial NOT NULL,
 p_id_name character varying(75),
 project_name character varying(75),
 project_id integer,
 number_of_processes integer,
 p_id_number character varying(30),
 CONSTRAINT project_pkey PRIMARY KEY (p_id_id ),
 CONSTRAINT name_ UNIQUE (p_id_name )
)
WITH (
 OIDS=FALSE
);
ALTER TABLE project.project
 OWNER TO postgres;

-- Trigger: pidnum on project.project

-- DROP TRIGGER pidnum ON project.project;


Help would be appreciated.

Bob



-Original Message- 
From: Adrian Klaver

Sent: Wednesday, July 18, 2012 5:47 PM
To: Bob Pawley
Cc: Alan Hodgson ; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Trouble with NEW

On 07/18/2012 03:20 PM, Bob Pawley wrote:

When I substitute new.fluid_id for the actual fluid)id the expression
returns the right value.


Huh? I thought that was what was causing the problem. From your original
post:


where p_id.fluids.fluid_id = NEW.fluid_id;

I receive an error ”record “new” has no field “fluid_id” “.


Per previous suggestion can we see?:

1)The trigger definition

2) The function.



--
Adrian Klaver
adrian.kla...@gmail.com 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Trouble with NEW

[Bob Pawley]

In all my reading of new and old I never made that connection.

Thanks Adrian

Bob

-Original Message- 
From: Adrian Klaver 
Sent: Thursday, July 19, 2012 6:50 AM 
To: Bob Pawley 
Cc: Alan Hodgson ; pgsql-general@postgresql.org 
Subject: Re: [GENERAL] Trouble with NEW 


On 07/19/2012 06:43 AM, Bob Pawley wrote:

The function is too long to copy.

I separated it into another trigger function with just the update
statement.

Here is the error -

ERROR: record new has no field fluid_id
SQL state: 42703
Context: SQL statement update p_id.fluids
set fluid_short =
(select shape.text
from  shape, num_search
where (select st_within(shape.wkb_geometry,
st_geometryn(num_search.the_geom4, 1)) = 'true')
and text !~ '[0-9]')
-
Here is the trigger function -

CREATE OR REPLACE FUNCTION fluid_name()
  RETURNS trigger AS $$

begin
update p_id.fluids
set fluid_short =
(select shape.text
from  shape, num_search
where (select st_within(shape.wkb_geometry,
st_geometryn(num_search.the_geom4, 1)) = 'true')
and text !~ '[0-9]')
where p_id.fluids.fluid_id = NEW.fluid_id;


  RETURN NULL;
End;

$$  LANGUAGE plpgsql;

create trigger r_fluidname after insert on project.project
for each row execute procedure fluid_name();


Here is the trigger script -

-- Trigger: r_fluidname on project.project

-- DROP TRIGGER r_fluidname ON project.project;

CREATE TRIGGER r_fluidname
  AFTER INSERT
  ON project.project
  FOR EACH ROW
  EXECUTE PROCEDURE public.fluid_name();


Here is the function script -

-- Function: public.fluid_name()

-- DROP FUNCTION public.fluid_name();

CREATE OR REPLACE FUNCTION public.fluid_name()
  RETURNS trigger AS
$BODY$

begin
update p_id.fluids
set fluid_short =
(select shape.text
from  shape, num_search
where (select st_within(shape.wkb_geometry,
st_geometryn(num_search.the_geom4, 1)) = 'true')
and text !~ '[0-9]')
where p_id.fluids.fluid_id = NEW.fluid_id;


  RETURN NULL;
End;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.fluid_name()
  OWNER TO postgres;


Here is the triggering table -

-- Table: project.project

-- DROP TABLE project.project;

CREATE TABLE project.project
(
  p_id_id serial NOT NULL,
  p_id_name character varying(75),
  project_name character varying(75),
  project_id integer,
  number_of_processes integer,
  p_id_number character varying(30),
  CONSTRAINT project_pkey PRIMARY KEY (p_id_id ),
  CONSTRAINT name_ UNIQUE (p_id_name )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE project.project
  OWNER TO postgres;

-- Trigger: pidnum on project.project

-- DROP TRIGGER pidnum ON project.project;


Help would be appreciated.


There is no fluid_id in the project.project table. The trigger NEW 
record only pulls from the table the trigger is attached to. You will 
have to find some other way of relating the project table to the fluids 
table.




Bob






--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Trouble with NEW

[Bob Pawley]

Hi

I would appreciate some fresh eyes on this expression -

update p_id.fluids
set fluid_short =
(select shape.text
from  shape, num_search
where (select st_within(shape.wkb_geometry, 
st_geometryn(num_search.the_geom4, 1)) = 'true')
and text !~ '[0-9]')
where p_id.fluids.fluid_id = NEW.fluid_id; 

I receive an error ”record “new” has no field “fluid_id” “.

Bob

Re: [GENERAL] Trouble with NEW

[Bob Pawley]

It's an insert after trigger function.

The table has a column named fluid_id.

Bob

-Original Message- 
From: Alan Hodgson

Sent: Wednesday, July 18, 2012 11:15 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Trouble with NEW

On Wednesday, July 18, 2012 11:07:34 AM Bob Pawley wrote:

Hi

I would appreciate some fresh eyes on this expression -

update p_id.fluids
set fluid_short =
(select shape.text
from  shape, num_search
where (select st_within(shape.wkb_geometry,
st_geometryn(num_search.the_geom4, 1)) = 'true') and text !~ '[0-9]')
where p_id.fluids.fluid_id = NEW.fluid_id;

I receive an error ”record “new” has no field “fluid_id” “.



Is that run within an insert or update trigger function? Does the table it's
on have a field named fluid_id?


Bob


--
When the Athenians finally wanted not to give to society but for society to
give to them, when the freedom they wished for most was freedom from
responsibility, then Athens ceased to be free and was never free again.” -- 
Edward Gibbon



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Trouble with NEW

[Bob Pawley]

When I substitute new.fluid_id for the actual fluid)id the expression 
returns the right value.


Following is the table -

CREATE TABLE p_id.fluids
(
 p_id_id integer,
 fluid_id serial,
 text_arrow geometry,
 line geometry,
 ip_op_equipment character varying(3),
 pipe_number character varying(100),
 pipe_size character varying,
 pipe_size_unit varchar (30),
 pipe_schedule varchar (30),
 dest_process varchar (30),
 dest_pump varchar (30),
 dest_pid varchar (30),
 source_process varchar (30),
 source_pump varchar (30),
 source_pid varchar (30),
 fluid_short character varying (10),
 fluid_name character varying(100),
 pump1 character varying(5),
 pump2 character varying(5),

 CONSTRAINT fluid_pk PRIMARY KEY (fluid_id)
);

Bob
-Original Message- 
From: Adrian Klaver

Sent: Wednesday, July 18, 2012 1:07 PM
To: Bob Pawley
Cc: Alan Hodgson ; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Trouble with NEW

On 07/18/2012 12:28 PM, Bob Pawley wrote:

It's an insert after trigger function.

The table has a column named fluid_id.


Can we see the table schema. What I am looking for is quoted column name
that would preserve case.



Bob




--
Adrian Klaver
adrian.kla...@gmail.com 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] DDL Triggers

[Bob Pawley]

Could someone point me to documentation regarding DDL triggers in Postgresql?

Bob

Re: [GENERAL] DDL Triggers

[Bob Pawley]

I have a table that is generated through ogr2ogr.

To get ogr2ogr working the way I want, I need to use the -overwrite 
function. If I use the append function information is lost. Something to do 
with the way the switches work.


Overwrite drops the existing table and also the attached trigger .

I am attempting to have the dropped table trigger a function as it is being 
populated by insert commands.


DDL trigger was mentioned as a possibility on the GDAL list.

Any suggestions will be welcome.

Bob


-Original Message- 
From: Adrian Klaver

Sent: Wednesday, February 15, 2012 8:36 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] DDL Triggers

On 02/15/2012 08:25 AM, Bob Pawley wrote:

Could someone point me to documentation regarding DDL triggers in
Postgresql?


You are going to need to be more specific. Are you talking triggers that
are activated by a DDL statement or a trigger that creates a DDL
statement? Also what pl language(s) are you using?


Bob



--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Triggering a function on table overwrite

[Bob Pawley]

Hi

Normally when I need to run a function during an insert I make it a trigger 
function of that table.

However, in this case, I need to overwrite the table with which the trigger is 
attached.

I would appreciate any suggestions on how to do this.

Bob

[GENERAL] DB Dump

[Bob Pawley]

Hi

PostgreSQL v9.0
Win 7

I am using the following to dump a database.

I get an error – “could not open output file “.backup”. Permission 
denied.”

To me, this sounds as if I am required to create an output file in order to get 
an output file.

In previous versions the output file was created as part of the transaction.

What am I missing?

pg_dump -h localhost -p 5432 -U postgres -v -f PDW_cp_Nov_2011.backup PDW

Bob

Re: [GENERAL] DB Dump

[Bob Pawley]

The file doesn't exist.

Just in case, I modified the dump to pg_dump -h localhost -p 5432 -U 
postgres -v -f PDW2_cp_Nov_2011.backup PDW and got the same error.


Bob

-Original Message- 
From: Guillaume Lelarge

Sent: Wednesday, November 09, 2011 9:48 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] DB Dump

On Wed, 2011-11-09 at 08:58 -0800, Bob Pawley wrote:

Hi

PostgreSQL v9.0
Win 7

I am using the following to dump a database.

I get an error – “could not open output file “.backup”. Permission 
denied.”


To me, this sounds as if I am required to create an output file in order 
to get an output file.


In previous versions the output file was created as part of the 
transaction.


What am I missing?

pg_dump -h localhost -p 5432 -U postgres -v -f PDW_cp_Nov_2011.backup 
PDW




I suppose it means that the PDW_cp_Nov_2011.backup file already exists
but the OS user executing pg_dump has no right to overwrite it.


--
Guillaume
 http://blog.guillaume.lelarge.info
 http://www.dalibo.com 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] DB Dump

[Bob Pawley]

Following is a copy of the end of running the dump -

pg_dump: saving standard_conforming_strings = off
pg_dump: saving database definition
pg_dump: [archiver] could not open output file PDW_cp_Nov_2011.backup: 
Permiss

ion denied
pg_dump: *** aborted because of error

Bob

-Original Message- 
From: Guillaume Lelarge

Sent: Wednesday, November 09, 2011 9:48 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] DB Dump

On Wed, 2011-11-09 at 08:58 -0800, Bob Pawley wrote:

Hi

PostgreSQL v9.0
Win 7

I am using the following to dump a database.

I get an error – “could not open output file “.backup”. Permission 
denied.”


To me, this sounds as if I am required to create an output file in order 
to get an output file.


In previous versions the output file was created as part of the 
transaction.


What am I missing?

pg_dump -h localhost -p 5432 -U postgres -v -f PDW_cp_Nov_2011.backup 
PDW




I suppose it means that the PDW_cp_Nov_2011.backup file already exists
but the OS user executing pg_dump has no right to overwrite it.


--
Guillaume
 http://blog.guillaume.lelarge.info
 http://www.dalibo.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] DB Dump

[Bob Pawley]

I'm logged on to my computer as an administrator(and am the only user). What 
other permission do I need??


Bob

-Original Message- 
From: Adrian Klaver

Sent: Wednesday, November 09, 2011 10:00 AM
To: Bob Pawley
Cc: Guillaume Lelarge ; Postgresql
Subject: Re: [GENERAL] DB Dump

On 11/09/2011 09:58 AM, Bob Pawley wrote:

Following is a copy of the end of running the dump -

pg_dump: saving standard_conforming_strings = off
pg_dump: saving database definition
pg_dump: [archiver] could not open output file PDW_cp_Nov_2011.backup:
Permiss
ion denied
pg_dump: *** aborted because of error


You don't have permission to the directory where this file is being saved.


Bob




--
Adrian Klaver
adrian.kla...@gmail.com 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] DB Dump

[Bob Pawley]

Opening Command Prompt as administrator solved my problem.

Thanks everybody.

Bob

-Original Message- 
From: Raymond O'Donnell 
Sent: Wednesday, November 09, 2011 10:41 AM 
To: Bob Pawley 
Cc: Adrian Klaver ; Guillaume Lelarge ; Postgresql 
Subject: Re: [GENERAL] DB Dump 


On 09/11/2011 18:05, Bob Pawley wrote:

I'm logged on to my computer as an administrator(and am the only user).
What other permission do I need??



Well, does the administrator have write permission to the directory?

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Dump Error Message

[Bob Pawley]

Hi

I am attempting to dump a database using PostgreDAC.

I am getting the following error message which I don’t understand. 

Can someone shed some light on this?

“Error message from server: ERROR:  column tgisconstraint does not exist
LINE 1: ...c AS tgfname, tgtype, tgnargs, tgargs, tgenabled, tgisconstr...
 ^
FileHandler: The command was: SELECT tgname, tgfoid::pg_catalog.regproc AS 
tgfname, tgtype, tgnargs, tgargs, tgenabled, tgisconstraint, tgconstrname, 
tgdeferrable, tgconstrrelid, tginitdeferred, tableoid, oid, 
tgconstrrelid::pg_catalog.regclass AS tgconstrrelname FROM 
pg_catalog.pg_trigger t WHERE tgrelid = '19069'::pg_catalog.oid AND 
tgconstraint = 0”

When I execute this command in the SQL pane of PGAdmin and remove “AND 
tgconstraint = 0” the selct shows a row which is unfamiliar to me.

Bob

Re: [GENERAL] Are file system level differential/incremental backups possible?

[Bob Hatfield]

 Is it possible to do a full file system level backup of the data
 directory, say once a week, and differentials or incrementals daily?

 I'd love to be able to do this, but you can't do it usefully at a
 file-system level. There's too much churn in the data files for even a
 binary diff to be much use - and even if it were, the performance of it
 would be miserable.

Actually, for us, a full backup is currently about 950GB which takes
about 24 hours and a diff several days later is only 150GB and takes
two hours.  (It takes significantly less time not only becuase it's
less data but also because the diff job doesn't have to compete with
other backup jobs (since it's faster due to less data).)

I currently stop pg, robocopy (rsync) the changes to our DRS server,
restart pg -- then backup the data on the DRS server later.  This
allows us to have our database back up within 30 minutes.

I've tested the database after doing a restore: full + diff and it
seems fine (both viewing with the application as well as running a
reindex on the entire db).  I think we'll stick with this process with
the understanding that if a disaster struck, and we needed to do a
full + diff restore, that we may have a few files that are there that
that aren't needed.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg 8.3 replication causing corruption

[Bob Hatfield]

 Any movement on this? There is considerable interest in any known
 issues resolving reproducible issues with postgres replication.   Do
 you happen to remember if set up the standby when the master was under
 high load conditions?  Any interesting/unexplained messages in the
 standby logs?

I'm still trying to digest your last post AND I'm going to do some
more testing this weekend.  I do typically setup the standby while
master is under load -- I will try to set it up under light/no load
this weekend and test.

Some Logs:

head of postgresql...log on slave:
---
2011-09-27 02:24:54 MST LOG: could not open file
pg_xlog/0001059F00C7 (log file 1439, segment 199): No such
file or directory
2011-09-27 02:24:54 MST LOG: redo done at 59F/C6FFDB08
2011-09-27 02:24:54 MST LOG: last completed transaction was at log time
2011-09-26 23:55:29.746-07
2011-09-27 02:24:59 MST LOG: restored log file 0001059F00C6
from archive
2011-09-27 02:25:29 MST LOG: selected new timeline ID: 2
2011-09-27 02:25:59 MST LOG: archive recovery complete
2011-09-27 02:26:03 MST LOG: database system is ready to accept connections
2011-09-27 02:26:03 MST LOG: autovacuum launcher started

tail of STANDBY.LOG on slave after trigger:
---
WAL file not present yet. Checking for trigger file...
WAL file not present yet. Checking for trigger file...
WAL file not present yet. Checking for trigger file...trigger file found

Trigger file : P:\PROGRA~1\PostgreSQL\trigger.txt
Waiting for WAL file : 0001059F00C6
WAL file path : P:\PROGRA~1\PostgreSQL\archivewal\0001059F00C6
Restoring to... : pg_xlog\RECOVERYXLOG
Sleep interval : 5 seconds
Max wait interval : 0 forever
Command for restore : copy
P:\PROGRA~1\PostgreSQL\archivewal\0001059F00C6
pg_xlog\RECOVERYXLOG
Keep archive history :  and later
running restore : OK

Trigger file : P:\PROGRA~1\PostgreSQL\trigger.txt
Waiting for WAL file : 0002.history
WAL file path : P:\PROGRA~1\PostgreSQL\archivewal\0002.history
Restoring to... : pg_xlog\RECOVERYHISTORY
Sleep interval : 5 seconds
Max wait interval : 0 forever
Command for restore : copy
P:\PROGRA~1\PostgreSQL\archivewal\0002.history
pg_xlog\RECOVERYHISTORY
Keep archive history :  and later
running restore :not restored : history file not found

Trigger file : P:\PROGRA~1\PostgreSQL\trigger.txt
Waiting for WAL file : 0001.history
WAL file path : P:\PROGRA~1\PostgreSQL\archivewal\0001.history
Restoring to... : pg_xlog\RECOVERYHISTORY
Sleep interval : 5 seconds
Max wait interval : 0 forever
Command for restore : copy
P:\PROGRA~1\PostgreSQL\archivewal\0001.history
pg_xlog\RECOVERYHISTORY
Keep archive history :  and later
running restore :not restored : history file not found

Tail of postgresql...log on master before/during shutdown for backups:
-
2011-10-04 01:17:02 MST LOG: unexpected EOF on client connection
2011-10-04 01:17:11 MST LOG: received fast shutdown request
2011-10-04 01:17:11 MST LOG: aborting any active transactions
2011-10-04 01:17:11 MST FATAL: terminating connection due to administrator
command
2011-10-04 01:17:11 MST FATAL: terminating connection due to administrator
command
2011-10-04 01:17:12 MST FATAL: terminating connection due to administrator
command
2011-10-04 01:17:12 MST FATAL: terminating connection due to administrator
command
2011-10-04 01:17:12 MST FATAL: terminating connection due to administrator
command
2011-10-04 01:17:12 MST FATAL: terminating connection due to administrator
command
2011-10-04 01:17:12 MST FATAL: terminating connection due to administrator
command
2011-10-04 01:17:12 MST LOG: autovacuum launcher shutting down
2011-10-04 01:17:12 MST LOG: shutting down
2011-10-04 01:17:12 MST LOG: database system is shut down

Head of postgresql...log on master starting up after stopping/starting
master's pg process:
--
2011-10-04 02:19:37 MST LOG: database system was shut down at 2011-10-04
01:17:12 MST
2011-10-04 02:19:37 MST LOG: database system is ready to accept connections
2011-10-04 02:19:37 MST LOG: autovacuum launcher started
2011-10-04 02:19:37 MST LOG: loaded library
$libdir/plugins/plugin_debugger.dll

end of post

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg 8.3 replication causing corruption

[Bob Hatfield]

 have you had any power events?  hard shutdowns, etc? I wonder if the problem 
 is in the clog files, and not the heap itself.

Nothing unusual for as long as I can tell.  Reminder that as long as I
don't restart the primary's pg process, everything works fine
(secondary's data is intact).

It's as if stopping/starting the primary causes a shipped wal file to
be corrupt or contain duplicated data then processed by the secondary.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Are file system level differential/incremental backups possible?

[Bob Hatfield]

 If you drop or truncate a table between the full and the incremental backup, 
 will that file be resurrected?

 Such resurrected files will not disturb PostgreSQL, but if you keep them 
 around, you might end up with a lot of dead files if you have to restore a 
 couple of times.

That makes sense, thank you.  At one point, I was doing delta copies
of the database: full one night, then only new/changed files the
following nights -- I am pretty sure that didn't work and thought it
was because the destination had extra files that pg had removed on the
primary.  I now use a robocopy /mir which removes files on the
destination that aren't on the primary.  This seems to work fine --
but it made me think that PostreSQL doesn't work with the former
process.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg 8.3 replication causing corruption

[Bob Hatfield]

 Should replication cause corruption on the secondary when stopping/starting 
 the primary?

 I wasn't aware 8.3 had any built in replication?  what sort of replication 
 add-ons are you using?


Continuous archiving / WAL shipping as described in:
http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Are file system level differential/incremental backups possible?

[Bob Hatfield]

Is it possible to do a full file system level backup of the data
directory, say once a week, and differentials or incrementals daily?

I'm wondering if there are files that would normally be removed that a
restore: Full then diff/inc would not remove and perhaps
corrupt/confuse things.

Process:
Saturday: Full backup (reset archive bits) of data dir with database shutdown
Sunday: Differential (don't reset archive bits) of data dir with
database shutdown
Monday: Differential (don't reset archive bits) of data dir with
database shutdown
Wednesday: Restore to test server using Saturday's Full and Monday's
Differential.

Obviously this works for regular files/file systems; however, I'm not
sure this is a good method with postgresql as the resulting data dir
*may* (?) contain extra files (or other issues)?

Note: our database is 850GB (Windows 2008 R2 pg version 8.3.12)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg 8.3 replication causing corruption

[Bob Hatfield]

 Should replication cause corruption on the secondary when stopping/starting 
 the primary?

 I wasn't aware 8.3 had any built in replication?  what sort of replication 
 add-ons are you using?


Continuous archiving / WAL shipping as described in:
http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg 8.3 replication causing corruption

[Bob Hatfield]

 Should replication cause corruption on the secondary when stopping/starting
 the primary?  (pg 8.3.12, windows 2008 R2 on both servers)

 No, it shouldn't. Any duplicate keys would represent a serious error.

 It sounds like you're using warm standby, but when you say run
 pg_start_backup() AFTER each nightly backup I admit to being confused.


Thanks for your response. Perhaps a quick process flow would help clarify:

Scenario 1 (no errors):
a) create warm standby and let run throughout the day (works great)
b) at the end of the day, trigger the secondary and run a reindex for
testing (no errors)

Scenario 2 (errors):
a) create warm standby and let run throughout the day (works great)
b) nightly backup: shutdown pg on primary, do a file system copy (for
backup later), start pg again on primary
c) the next morning, trigger the secondary and run a re-index for
testing (ERRORS as described in thread)

Side note: the data copied in 2.b is fine and also passes a full re-index.

Scenario 3 (work around - not a very good one):
a) create warm standby and let run throughout the day (works great)
b) nightly backup: shutdown pg on primary, do a file system copy (for
backup later), start pg again on primary
c) the next morning, re-create the warm standby  (this is where I may
have confused you with doing a pg_start_backup after nightly backups)

Thanks!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Are file system level differential/incremental backups possible?

[Bob Hatfield]

 Anyway, a better way for you would be to do a regular backup (with 
 pg_start_backup, copy and pg_stop_backup) and then use wal archive_command to 
 keep the xlogs between 2 full backups.

Thanks Julien.  Can pg_start/stop_backup() be used for regular full
file system backups?   All of the documentation I've read only refers
to using those for warm standby/wal shipping methods.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg 8.3 replication causing corruption

[Bob Hatfield]

 Something about your setup is suspect. Disks perhaps.

Disk: Fusion IOdrive (1.2TB NAND drive)

I've read that one should set wal_sync_method=fsync_writethrough for
Windows servers.  It's currently set to open_datasync, I have no idea
what effect that will have other than I've read less performance.

Thoughts?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Are file system level differential/incremental backups possible?

[Bob Hatfield]

 The base backup necessary to initialize a warm standby server is a full file 
 system backup of the database, which can also be used for restores to any 
 point in time after the base backup is completed, assuming you also have all 
 the archived WAL files.

Thanks to both of  you.  I currently use the base backup technique for
use with a warm standby server but was not aware I could use that for
full file system level backups as well.

In fact, I currently run a warm standby server (created from a base
backup every several weeks) as well as stop/start the pg process to do
a full file system level copy each night.  If I think about this a
bit, I'm sure there's a more efficient way of doing this.  (As well as
may solve another problem I'm having (another post) with the standby
server's database getting corrupt after stopping/starting the
primary's pg process.)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg 8.3 replication causing corruption

[Bob Hatfield]

Should replication cause corruption on the secondary when stopping/starting
the primary?  (pg 8.3.12, windows 2008 R2 on both servers)



Everything seems to work OK, but when we trigger the replication server, it
doesn’t pass a reindex.  (Errors with can’t create unique indexes due to
duplicate values.)



I detailed the logs in another post (link below), but fear my verbosity may
have scared people off!  :)

http://archives.postgresql.org/pgsql-general/2011-10/msg00121.php



Right now, our only solution seems to be to do another pg_start_backup() -*
copy - pg_stop*_backup()  after each nightly backup.



I'm doing something wrong Thanks for any help/pointers!



*-Bob Hatfield*

[GENERAL] Replication/WAL shipping. db errors on slave after stop/start master

[Bob Hatfield]

Reindex of triggered (slave) database showing errors.

We have replication working in 8.3.12 on two identical Windows 2008 R2
servers.  Anytime I trigger the slave, it comes up fine and doing a reindex
of the slave database results in no errors.  However, when I do this *after*
our nightly backup routine (which *fast* stops pg to do a full file system
backup then starts it) a reindex on the salve results in errors similar to:

ERROR:  could not create unique index xyz_pkey

DETAIL:  Table contains duplicated values.



** Error **



ERROR: could not create unique index xyz_pkey

SQL state: 23505

Detail: Table contains duplicated values.



Where xyz is the table name (real name removed) and it's not always on the
same table.


Note that I accidentally discovered this have attempting a reindex on the
triggered slave database.


-Bob


Logs:

head of postgresql...log on slave:
---
2011-09-27 02:24:54 MST LOG:  could not open file
pg_xlog/0001059F00C7 (log file 1439, segment 199): No such
file or directory
2011-09-27 02:24:54 MST LOG:  redo done at 59F/C6FFDB08
2011-09-27 02:24:54 MST LOG:  last completed transaction was at log time
2011-09-26 23:55:29.746-07
2011-09-27 02:24:59 MST LOG:  restored log file 0001059F00C6
from archive
2011-09-27 02:25:29 MST LOG:  selected new timeline ID: 2
2011-09-27 02:25:59 MST LOG:  archive recovery complete
2011-09-27 02:26:03 MST LOG:  database system is ready to accept connections
2011-09-27 02:26:03 MST LOG:  autovacuum launcher started


tail of STANDBY.LOG on slave after trigger:
--
WAL file not present yet. Checking for trigger file...
WAL file not present yet. Checking for trigger file...
WAL file not present yet. Checking for trigger file...trigger file found

Trigger file : P:\PROGRA~1\PostgreSQL\trigger.txt
Waiting for WAL file : 0001059F00C6
WAL file path : P:\PROGRA~1\PostgreSQL\archivewal\0001059F00C6
Restoring to... : pg_xlog\RECOVERYXLOG
Sleep interval : 5 seconds
Max wait interval : 0 forever
Command for restore : copy
P:\PROGRA~1\PostgreSQL\archivewal\0001059F00C6
pg_xlog\RECOVERYXLOG
Keep archive history :  and later
running restore : OK

Trigger file : P:\PROGRA~1\PostgreSQL\trigger.txt
Waiting for WAL file : 0002.history
WAL file path : P:\PROGRA~1\PostgreSQL\archivewal\0002.history
Restoring to... : pg_xlog\RECOVERYHISTORY
Sleep interval : 5 seconds
Max wait interval : 0 forever
Command for restore : copy
P:\PROGRA~1\PostgreSQL\archivewal\0002.history
pg_xlog\RECOVERYHISTORY
Keep archive history :  and later
running restore :not restored : history file not found

Trigger file : P:\PROGRA~1\PostgreSQL\trigger.txt
Waiting for WAL file : 0001.history
WAL file path : P:\PROGRA~1\PostgreSQL\archivewal\0001.history
Restoring to... : pg_xlog\RECOVERYHISTORY
Sleep interval : 5 seconds
Max wait interval : 0 forever
Command for restore : copy
P:\PROGRA~1\PostgreSQL\archivewal\0001.history
pg_xlog\RECOVERYHISTORY
Keep archive history :  and later
running restore :not restored : history file not found

Tail of postgresql on master before/during shutdown for backups:
-
2011-10-04 01:17:02 MST LOG:  unexpected EOF on client connection
2011-10-04 01:17:11 MST LOG:  received fast shutdown request
2011-10-04 01:17:11 MST LOG:  aborting any active transactions
2011-10-04 01:17:11 MST FATAL:  terminating connection due to administrator
command
2011-10-04 01:17:11 MST FATAL:  terminating connection due to administrator
command
2011-10-04 01:17:12 MST FATAL:  terminating connection due to administrator
command
2011-10-04 01:17:12 MST FATAL:  terminating connection due to administrator
command
2011-10-04 01:17:12 MST FATAL:  terminating connection due to administrator
command
2011-10-04 01:17:12 MST FATAL:  terminating connection due to administrator
command
2011-10-04 01:17:12 MST FATAL:  terminating connection due to administrator
command
2011-10-04 01:17:12 MST LOG:  autovacuum launcher shutting down
2011-10-04 01:17:12 MST LOG:  shutting down
2011-10-04 01:17:12 MST LOG:  database system is shut down

Head of postgresql on master starting up after backups:
---
2011-10-04 02:19:37 MST LOG:  database system was shut down at 2011-10-04
01:17:12 MST
2011-10-04 02:19:37 MST LOG:  database system is ready to accept connections
2011-10-04 02:19:37 MST LOG:  autovacuum launcher started
2011-10-04 02:19:37 MST LOG:  loaded library
$libdir/plugins/plugin_debugger.dll

[GENERAL] Searching through trigger functions

[Bob Pawley]

Hi

I have an error somewhere in one of the trigger functions in my database. The 
error message only told me it was a ‘Select * From (table name).

Is there a global method of finding where this expression resides other than 
searching each script individually.

Bob

[GENERAL] Arrays

[Bob Pawley]

Hi

Is there a method of counting the number of elements in an array??

Bob

[GENERAL] PSQLRestore

[Bob Pawley]

Hi

I’m having problems making PostgreDAC’s PSQLRestore work without an access 
violation.

Could someone please point me to an example code that I can check?

(My PSQLDump is working well, so I am using a valid restore file.)

Bob

Re: [GENERAL] PSQLRestore

[Bob Pawley]

-Original Message- 
From: Adrian Klaver

Sent: Thursday, September 08, 2011 1:41 PM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] PSQLRestore

On 09/08/2011 09:09 AM, Bob Pawley wrote:

Hi
I’m having problems making PostgreDAC’s PSQLRestore work without an
access violation.


The exact error message is?

Access violation at adress 74FF8E0F in module 'msvcrt.dll'. Write os address 
004F8574.



Could someone please point me to an example code that I can check?
(My PSQLDump is working well, so I am using a valid restore file.)


But are you restoring as a valid user?

I'm not sure.

The properties only refer to a superuser which doesn't appear to be 
applicable for a simple restore.


I am going through a PSQLDatabase connection in my normal manner.

The problem seems to be in this code which I am using to transfer from the 
opendialogue to PSQLRestore.


FileRestore :=  OpenDialog1.FileName;
 PSQLRestore1.RestoreFromFile(FileRestore, '  ');

I'm not sure what string is expected.

Bob


Bob



--
Adrian Klaver
adrian.kla...@gmail.com 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PSQLRestore

[Bob Pawley]

-Original Message- 
From: Raymond O'Donnell 
Sent: Thursday, September 08, 2011 3:23 PM 
To: Bob Pawley 
Cc: Adrian Klaver ; Postgresql 
Subject: Re: [GENERAL] PSQLRestore 


On 08/09/2011 23:02, Bob Pawley wrote:

The problem seems to be in this code which I am using to transfer from
the opendialogue to PSQLRestore.

FileRestore :=  OpenDialog1.FileName;
 PSQLRestore1.RestoreFromFile(FileRestore, '  ');

I'm not sure what string is expected.


Hello Bob,

According to the docs, the second argument should either be a TStrings
object, into which log messages are placed, or else the name of a file
where the log output can be written.

Ray.

Hi Ray

Is there any chance you could send me a short example of what that means.

If so, much appreciated.

Bob



--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Variable column name

[Bob Pawley]

-Original Message- 
From: Bill Moran

Sent: Thursday, September 01, 2011 8:19 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Variable column name

http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html
Section 39.5.4

If you're not familiar with plpgsql at all, you might want to start with
this:
http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html


Thanks for the suggestion.

Following is my interpretation of what I have read.

I am getting an error  -- column 1 does not exist

Could someone point to what I am doing wrong?

Bob

 Select 2 into point_array ;
   Select 1 into column ;

   Loop

   Execute 'Update library.compare Set'
   || quote_ident (column[point_array])
   || '=
(select st_distance (st_geometryn(public.similar.the_geom, 1),
(st_geometryn(public.similar.the_geom, point_array)))/ 
public.similar.prime

 from public.similar
 where public.similar.sight_description = ''H_Line'')'
--   || newvalue
   || 'from public.import_process_transfer'
   || 'where library.compare.process_id = 
public.import_process_transfer.process_id';

 --  || quote_literal();

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Variable column name

[Bob Pawley]

-Original Message- 
From: Raymond O'Donnell

Sent: Friday, September 02, 2011 10:38 AM
To: Bob Pawley
Cc: Bill Moran ; Postgresql
Subject: Re: [GENERAL] Variable column name

On 02/09/2011 18:33, Bob Pawley wrote:



-Original Message-

From: Bill Moran

Sent: Thursday, September 01, 2011 8:19 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Variable column name

http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html
Section 39.5.4

If you're not familiar with plpgsql at all, you might want to start with
this:
http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html


Thanks for the suggestion.

Following is my interpretation of what I have read.

I am getting an error  -- column 1 does not exist

Could someone point to what I am doing wrong?

Bob

 Select 2 into point_array ;
   Select 1 into column ;


Hi Bob,

I think it is the double-quotes around the 1; just leave them out to get
a literal integer 1:

 select 1 into column;

If I understand correctly, the double-quotes make Postgres look for a
column named 1.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

Ray
I've named  columns 1 through 10 so that it will be easy to determine the 
next column in the loop.

When I use the following it works well.

Update library.compare
Set 1[2] =
(select st_distance (st. 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Variable column name

[Bob Pawley]

-Original Message- 
From: Bill Moran

Sent: Friday, September 02, 2011 10:53 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Variable column name

In response to Bob Pawley rjpaw...@shaw.ca:


I am getting an error  -- column 1 does not exist


snip


Select 1 into column ;


Where are you selecting 1 from?  This query has no FROM clause, so of
course the column doesn't exist.

The previous query, SELECT 2 INTO point_array is going to put the
integer value 2 into the variable point_array, which I'm guessing is
not what you want either.


Well, actually that is what I am attempting.

I added the from clause and that seems to be acceptable for the column 
identification.


What I am trying to accomplish is to collect distance information between 
numerous geometries (in this case 8) at the first spatial location and build 
an array in column 1, one array point at a time.


Then the loop moves to the next location, establishes the geometries and 
updates the column 2 array with these distances.


It seems to work when I hard code the column name and array point, so I was 
hoping to make it work through a loop using variables for column and array 
point.


Does this make sense??

Bob 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Variable column name

[Bob Pawley]

Hi

I want to add information to multiple columns (20 – 40) by employing a loop. 
Each pass of the loop will populate one column with an array.

I have tried and I have read that variables can not be used to control column 
names. 

Is there a means of working around this restriction other than creating 20 to 
40 hard coded statements??

Bob 

Re: [GENERAL] Variable column name

[Bob Pawley]

-Original Message- 
From: Bill Moran

Sent: Thursday, September 01, 2011 7:59 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Variable column name

In response to Bob Pawley rjpaw...@shaw.ca:


I want to add information to multiple columns (20 – 40) by employing a 
loop. Each pass of the loop will populate one column with an array.


I have tried and I have read that variables can not be used to control 
column names.


Is there a means of working around this restriction other than creating 20 
to 40 hard coded statements??


You can generate dynamic SQL statements with plpgsql.

Would it be possible for you to point me to an example??

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/ 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Updating Arrays

[Bob Pawley]

Hi

I exploring the use of arrays.

So far I have created a table and inserted a row and updated the row with an 
array
Update library.compare 
  set _base = 
  '{2, 0.764149497122068, 4.8886}'
  where process_id = 2;
– successfully.

However when I attempt to update a specific element of the array 

Update library.compare 
set _base[2] = 
'{2}'
where process_id = 2;
I get an error “invalid input syntax for type numeric: {2}”

Bob

Create table library.compare (
id serial UNIQUE,
process_id int4,
device_id int4, 
_base decimal[] 
);
Insert into library.compare (process_id) 
values ('2');

Re: [GENERAL] Updating Arrays

[Bob Pawley]

I'm not sure what you mean.

_base -- not base -- is the column name.

Bob

-Original Message- 
From: Merlin Moncure

Sent: Monday, August 22, 2011 11:03 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Updating Arrays

On Mon, Aug 22, 2011 at 12:54 PM, Bob Pawley rjpaw...@shaw.ca wrote:

Hi

I exploring the use of arrays.

So far I have created a table and inserted a row and updated the row with 
an

array
Update library.compare
  set _base =
  '{2, 0.764149497122068, 4.8886}'
  where process_id = 2;
– successfully.

However when I attempt to update a specific element of the array

Update library.compare
set _base[2] =
'{2}'
where process_id = 2;
I get an error “invalid input syntax for type numeric: {2}”


try set base[2]  = 2

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgredac Dump

[Bob Pawley]

Hi Raghu

Thanks for the site. 

I am still having problems. 

The following downloads a file of 0 bytes.

  procedure TForm1.Button3Click(Sender: TObject);
begin
   PSQLDump1.DumpToFile('E5R', 'C:\PDW\E5R');
end;

(E5R is the database name.)

The Dump component points to a viable database.

I left the schema and tables empty as I understand that doing so will dump the 
whole database.

Any help would be appreciated.

Bob

From: raghu ram 
Sent: Thursday, May 12, 2011 11:36 AM
To: Bob Pawley 
Cc: Postgresql 
Subject: Re: [GENERAL] Postgredac Dump




On Thu, May 12, 2011 at 10:02 PM, Bob Pawley rjpaw...@shaw.ca wrote:

  Hi

  Can anyone point me to an example of how to use the Postgresdac Dump 
component??



Below URL will give more detail information about Postgresdac dump::

http://www.microolap.com/products/connectivity/postgresdac/help/TPSQLDump/Methods/DumpToFile.htm
 

--Raghu Ram

Re: [GENERAL] Postgredac Dump

[Bob Pawley]

The last log entry was two hours before I attempted to dump.

Bob

-Original Message- 
From: Raymond O'Donnell 
Sent: Friday, May 13, 2011 10:44 AM 
To: Bob Pawley 
Cc: raghu ram ; Postgresql 
Subject: Re: [GENERAL] Postgredac Dump 


On 13/05/2011 18:30, Bob Pawley wrote:

Hi Raghu
Thanks for the site.
I am still having problems.
The following downloads a file of 0 bytes.
procedure TForm1.Button3Click(Sender: TObject);
begin
PSQLDump1.DumpToFile('E5R', 'C:\PDW\E5R');
end;
(E5R is the database name.)
The Dump component points to a viable database.
I left the schema and tables empty as I understand that doing so will
dump the whole database.


Hi Bob,

I've only recently started using that component set, and haven't tried 
the pg_dump one yet, so I'm only guessing, but it sounds as if something 
is going wrong at the server end - maybe an authentication problem? What 
does the PostgreSQL log say?


Ray.

--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgredac Dump

[Bob Pawley]

Hi Ray

I finally got it to dump.

I switched the argument so that the path is first and the file name is after 
the comma.


I found this hint on a very useful site that shows a number of examples of 
PostgresDAC.


http://www.keashsoft.com/postgrestutorial/4.html

Bob

-Original Message- 
From: Raymond O'Donnell

Sent: Friday, May 13, 2011 10:44 AM
To: Bob Pawley
Cc: raghu ram ; Postgresql
Subject: Re: [GENERAL] Postgredac Dump

On 13/05/2011 18:30, Bob Pawley wrote:

Hi Raghu
Thanks for the site.
I am still having problems.
The following downloads a file of 0 bytes.
procedure TForm1.Button3Click(Sender: TObject);
begin
PSQLDump1.DumpToFile('E5R', 'C:\PDW\E5R');
end;
(E5R is the database name.)
The Dump component points to a viable database.
I left the schema and tables empty as I understand that doing so will
dump the whole database.


Hi Bob,

I've only recently started using that component set, and haven't tried
the pg_dump one yet, so I'm only guessing, but it sounds as if something
is going wrong at the server end - maybe an authentication problem? What
does the PostgreSQL log say?

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postgredac Dump

[Bob Pawley]

Hi

Can anyone point me to an example of how to use the Postgresdac Dump component??

Bob

[GENERAL] Urgent Order

[Bob Wilson]

Hello
This is Bob and I will like to order ( Indexing Table )Do get back to
me with the types and cost for the ones you do carry and let me know
if there is an extra cost when using visa or master Card.Kindly get
back  to me with your name Are you the sales manager or the Owner?
Regards
Bob Wilson

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Different views of remote server

[Bob Pawley]

Yes it shows only one server on the remote computer.

I can send the table as an sql dump if the list will accept an attachment.

The dumped table contains the geom information that I can't see on the 
remote connection.


I restored that same dumped table into a different local database. Somewhere 
in the restore process those geoms somehow got lost.


Taken all together it does seem as if there is something wrong with the geom 
information. Even to my uneducated eye they look strange compared to the 
others in the table.


Four of the problem fields represent arrow heads. The fifth is a point.

Bob


-Original Message- 
From: Adrian Klaver

Sent: Thursday, April 21, 2011 7:22 PM
To: pgsql-general@postgresql.org
Cc: Bob Pawley ; Scott Marlowe
Subject: Re: [GENERAL] Different views of remote server

On Thursday, April 21, 2011 9:24:57 am Bob Pawley wrote:

Hi Scott

According to NSAuditor(www.nsauditor.com) there is only one server with
port 5432.

When I enter information into the remote database it shows up on the same
database that has this problem.

How do I determine my 'connection credentials'?


In pgAdmin they will be in the server properties. Look to see if you are
connecting to a port other than 5432.



Bob




--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Different views of remote server

[Bob Pawley]

Hi

A little more research.

I accessed the problem table as a remote connection using PGAdmin.

I selected the cell that shows as being null and copied and pasted the 
contents into Word.


The geom IS there.

Using this method the geom is also present, but not visible, in the table I 
am accessing as a local connection. This is the remote database which I 
dumped and then restored as a local.


These fields are not only not visible but they return null when I do a 
simple select query.


I could really do with some suggestions.

Bob

-Original Message- 
From: Adrian Klaver

Sent: Thursday, April 21, 2011 7:22 PM
To: pgsql-general@postgresql.org
Cc: Bob Pawley ; Scott Marlowe
Subject: Re: [GENERAL] Different views of remote server

On Thursday, April 21, 2011 9:24:57 am Bob Pawley wrote:

Hi Scott

According to NSAuditor(www.nsauditor.com) there is only one server with
port 5432.

When I enter information into the remote database it shows up on the same
database that has this problem.

How do I determine my 'connection credentials'?


In pgAdmin they will be in the server properties. Look to see if you are
connecting to a port other than 5432.



Bob




--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Different views of remote server

[Bob Pawley]

If it's empty space at the beginning it goes on for a long time.

Can I send the table as an sql dump as an attachment with the list??

Bob

-Original Message- 
From: Adrian Klaver

Sent: Friday, April 22, 2011 1:29 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org ; Scott Marlowe
Subject: Re: [GENERAL] Different views of remote server

On Friday, April 22, 2011 12:52:28 pm Bob Pawley wrote:

Hi

A little more research.

I accessed the problem table as a remote connection using PGAdmin.

I selected the cell that shows as being null and copied and pasted the
contents into Word.

The geom IS there.

Using this method the geom is also present, but not visible, in the table 
I

am accessing as a local connection. This is the remote database which I
dumped and then restored as a local.

These fields are not only not visible but they return null when I do a
simple select query.

I could really do with some suggestions.


Still waiting for a description of the table and the exact data type you are
using. Also are you sure it is really returning null and that you are not
looking at empty space at the beginning of the record?



Bob



--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Different views of remote server

[Bob Pawley]

Hi Adrian

I looked at the table in JEdit. The binary strings for the missing fields 
are considerably longer than the others.


What limiting factor have I probably exceeded??

Bob

-Original Message- 
From: Adrian Klaver

Sent: Wednesday, April 20, 2011 4:14 PM
To: pgsql-general@postgresql.org
Cc: Bob Pawley
Subject: Re: [GENERAL] Different views of remote server

On Wednesday, April 20, 2011 11:09:59 am Bob Pawley wrote:

Hi

This is probably going to turn out to be me doing something stupid, but-

I have two computers, one of which I use as a remote server for my
database.

When I connect to the remote database through my interface there are 
errors

that suggest a problem with the data in one of the tables.

Using PgAdmin to view the remote table there is indeed some information
missing (5 out of 16 geoms).

When I use the PGAdmin on the remote computer, using a local connection,
this information is not missing it is intact.

I don’t think it is a PgAdmin problem because when I do a ‘select the_geom’
as a remote query the information is missing where it is not missing the
PgAdmin on the other computer as a local query.

Also connecting to the remote DB using Quantum GIS shows the “missing”
information as being present and normal.

I have also checked with a server display app and the appropriate number 
of

servers are present (only one present on the remote computer).

I’ve run out of ideas - Would anyone have any thoughts of what might be
going on???


What is the schema for the table? What exactly is the data and do the 5
'missing' data differ markedly from the other data?



Bob


--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Different views of remote server

[Bob Pawley]

Hi Scott

According to NSAuditor(www.nsauditor.com) there is only one server with port 
5432.


When I enter information into the remote database it shows up on the same 
database that has this problem.


How do I determine my 'connection credentials'?

Bob

-Original Message- 
From: Scott Marlowe

Sent: Wednesday, April 20, 2011 7:38 PM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Different views of remote server

On Wed, Apr 20, 2011 at 1:09 PM, Bob Pawley rjpaw...@shaw.ca wrote:

Hi

This is probably going to turn out to be me doing something stupid, but-

I have two computers, one of which I use as a remote server for my 
database.


When I connect to the remote database through my interface there are 
errors

that suggest a problem with the data in one of the tables.

Using PgAdmin to view the remote table there is indeed some information
missing (5 out of 16 geoms).

When I use the PGAdmin on the remote computer, using a local connection,
this information is not missing it is intact.

I don’t think it is a PgAdmin problem because when I do a ‘select the_geom’
as a remote query the information is missing where it is not missing the
PgAdmin on the other computer as a local query.

Also connecting to the remote DB using Quantum GIS shows the “missing”
information as being present and normal.

I have also checked with a server display app and the appropriate number 
of

servers are present (only one present on the remote computer).

I’ve run out of ideas - Would anyone have any thoughts of what might be
going on???


You're likely connecting to a different database than you think you
are.  What do your connection credentials look like in each case?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Different views of remote server

[Bob Pawley]

Hi

This is probably going to turn out to be me doing something stupid, but-

I have two computers, one of which I use as a remote server for my database. 

When I connect to the remote database through my interface there are errors 
that suggest a problem with the data in one of the tables.

Using PgAdmin to view the remote table there is indeed some information missing 
(5 out of 16 geoms).

When I use the PGAdmin on the remote computer, using a local connection, this 
information is not missing it is intact.

I don’t think it is a PgAdmin problem because when I do a ‘select the_geom’ as 
a remote query the information is missing where it is not missing the PgAdmin 
on the other computer as a local query.

Also connecting to the remote DB using Quantum GIS shows the “missing” 
information as being present and normal.

I have also checked with a server display app and the appropriate number of 
servers are present (only one present on the remote computer).

I’ve run out of ideas - Would anyone have any thoughts of what might be going 
on???

Bob

PS – I have tried one other thing - dumping the database from the remote server 
and restoring it to the local server and the information (the 5 geoms) goes 
missing sometime during this transaction.

Re: [GENERAL] how to avoid repeating expensive computation in select

[Bob Price]

One last question.  Are there any pitfalls if I roll my own ability to check 
for duplicate calls?

Since I am using my own defined data type, and my own function, I could 
do this by:

1. in my data type X, adding fields for: a table oid, a row oid, a copy 
of a reference to the last 2nd argument, and a copy of the last result 
(in my case a double)

2. in my function (which takes two X args), if the new 
table/row/ref-2nd-arg match the last data (saved in the first arg), then 
return the last result, otherwise compute the new result and save this 
info to use on the next call

This should enable only a single computation of the data for any given 
row as long as the same args are used as parameters.

But, is this safe, or have any pitfalls I would need to look out for?

Thanks!
Bob


--- On Thu, 2/3/11, Tom Lane t...@sss.pgh.pa.us wrote:

 From: Tom Lane t...@sss.pgh.pa.us
 Subject: Re: [GENERAL] how to avoid repeating expensive computation in select
 To: Bob Price rjp_em...@yahoo.com
 Cc: Bill Moran wmo...@potentialtech.com, pgsql-general@postgresql.org
 Date: Thursday, February 3, 2011, 5:33 PM
 Bob Price rjp_em...@yahoo.com
 writes:
  If I set the COST of expensivefunc high, and label it
 IMMUTABLE, will the query executor note that the two
 invocations to expensivefunc have the same inputs so it can
 only call it once and re-use the result the second time?
 
 No.  There is a myth prevalent among certain wishful
 thinkers that
 IMMUTABLE does something like that, but it doesn't. 
 IMMUTABLE only
 licenses the planner to fold a call *with constant
 arguments* into a
 constant result, by executing the function once before the
 query
 actually starts.  Textually distinct calls of a
 function are not folded
 together in any case.
 

 regards, tom lane
 




  

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how to avoid repeating expensive computation in select

[Bob Price]

I have been searching through the docs and mailing list and haven't found a way 
to do this, so I thought I would ask the community.

I would like to know if there is a way in PostgreSQL to avoid repeating an 
expensive computation in a SELECT where the result is needed both as a returned 
value and as an expression in the WHERE clause.

As a simple example, consider the following query on a table with 'id' and 
'value' columns, and an expensive computation represented as a function:

  SELECT id, expensivefunc(value) AS score FROM mytable
 WHERE id LIKE '%z%' AND expensivefunc(value)  0.5;

It would be great if I could find a way to only compute expensivefunc(value) at 
most once per row, and not at all if the other WHERE constraints are not 
satisfied.

For this simple case I know that I could rewrite the SELECT as something like 
the following:

WITH other_where AS (
SELECT id, value FROM mytable WHERE id LIKE '%z%'
  ), calc_scores AS (
SELECT id, expensivefunc(value) AS score FROM other_where
  )
SELECT id, score from calc_scores WHERE score  0.5;

This works in this simple case, but my guess is that it probably adds a lot of 
overhead (is this true?), and I also have to deal with much more complicated 
scenarios with multiple expensive calculations that may not fit into this kind 
of rewrite.

Does anyone know of a simpler way to accomplish this?

For example, it would be great if there were a function that could reference 
the Nth select list item so it is only computed once, like:

  SELECT id, expensivefunc(value) AS score FROM mytable
 WHERE id LIKE '%z%' AND sel_list_item(2)  0.5;

or if there were temporary variables in the WHERE expressions like:

  SELECT id, tmp1 AS score FROM mytable
 WHERE id LIKE '%z%' AND (tmp1 = expensivefunc(value))  0.5;

Any ideas anyone!

Thanks in advance!
Bob



  

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how to avoid repeating expensive computation in select

[Bob Price]

I have been searching through the docs and mailing list and haven't found a way 
to do this, so I thought I would ask the community.

I would like to know if there is a way in PostgreSQL to avoid repeating an 
expensive computation in a SELECT where the result is needed both as a returned 
value and as an expression in the WHERE clause.

As a simple example, consider the following query on a table with 'id' and 
'value' columns, and an expensive computation represented as a function:

  SELECT id, expensivefunc(value) AS score FROM mytable
 WHERE id LIKE '%z%' AND expensivefunc(value)  0.5;

It would be great if I could find a way to only compute expensivefunc(value) at 
most once per row, and not at all if the other WHERE constraints are not 
satisfied.

For this simple case I know that I could rewrite the SELECT as something like 
the following:

WITH other_where AS (
SELECT id, value FROM mytable WHERE id LIKE '%z%'
  ), calc_scores AS (
SELECT id, expensivefunc(value) AS score FROM other_where
  )
SELECT id, score from calc_scores WHERE score  0.5;

This works in this simple case, but my guess is that it probably adds a lot of 
overhead (is this true?), and I also have to deal with much more complicated 
scenarios with multiple expensive calculations that may not fit into this kind 
of rewrite.

Does anyone know of a simpler way to accomplish this?

For example, it would be great if there were a function that could reference 
the Nth select list item so it is only computed once, like:

  SELECT id, expensivefunc(value) AS score FROM mytable
 WHERE id LIKE '%z%' AND sel_list_item(2)  0.5;

or if there were temporary variables in the WHERE expressions like:

  SELECT id, tmp1 AS score FROM mytable
 WHERE id LIKE '%z%' AND (tmp1 = expensivefunc(value))  0.5;

Any ideas anyone!

Thanks in advance!
Bob



  

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] how to avoid repeating expensive computation in select

[Bob Price]

Thanks for all of the responses.

If the IMMUTABLE flag on a function does what the docs say then it might 
satisfy my needs.

Here is a more specific example of what I need to do, with a new custom data 
type and a new function:

- define new complex data type X
- create table mytable ( id varchar, value X )
- create function expensivefunc(X,X) which is implemented in C
- select id, expensivefunc(value, 'constantdata...'::X) as score
   from mytable where expensivefunc(value, 'constantdata...'::X)  0.5;

If I set the COST of expensivefunc high, and label it IMMUTABLE, will the query 
executor note that the two invocations to expensivefunc have the same inputs so 
it can only call it once and re-use the result the second time?

I imagine that it might be a problem to pass 'constantdata...'::X to both 
invocations.  I guess that I could create a one-time use function that declared 
a variable with this 'constantdata...'::X value, and then pass this variable in 
both calls.

Would this work?

Thanks again!
Bob



--- On Thu, 2/3/11, Bill Moran wmo...@potentialtech.com wrote:

 From: Bill Moran wmo...@potentialtech.com
 Subject: Re: [GENERAL] how to avoid repeating expensive computation in select
 To: Bob Price rjp_em...@yahoo.com
 Cc: pgsql-general@postgresql.org
 Date: Thursday, February 3, 2011, 12:23 PM
 In response to Bob Price rjp_em...@yahoo.com:
 
  I have been searching through the docs and mailing
 list and haven't found a way to do this, so I thought I
 would ask the community.
  
  I would like to know if there is a way in PostgreSQL
 to avoid repeating an expensive computation in a SELECT
 where the result is needed both as a returned value and as
 an expression in the WHERE clause.
  
  As a simple example, consider the following query on a
 table with 'id' and 'value' columns, and an expensive
 computation represented as a function:
  
SELECT id, expensivefunc(value) AS
 score FROM mytable
   WHERE id LIKE '%z%' AND
 expensivefunc(value)  0.5;
  
  It would be great if I could find a way to only
 compute expensivefunc(value) at most once per row, and not
 at all if the other WHERE constraints are not satisfied.
 
 Two ways that I can think of:
 1) If expensivefunc() doesn't have any side-effects, you
 can create it
as IMMUTABLE, which tells PostgreSQL that
 it can cache the result
for optimization purposes. 
 IMMUTABLE is not the default.
 2) Create a new column in the table that stores the value
 of
expensivefunc(value) and add a trigger to
 the table to ensure that
column is updated any time value is
 changed.  This will slow down
inserts and updates a bit, but it means
 you can select/compare the
generated column directly with no
 calculation.
 
 Which one of these is more practical for you depends on a
 number of
 factors about the table, the data, and the function.
 
 -- 
 Bill Moran
 http://www.potentialtech.com
 http://people.collaborativefusion.com/~wmoran/
 


  

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Remote Connection

[Bob Pawley]

Hi Bill

Thanks for answering.

The problem turned out to be the excessive permissions required in Windows 7 
Firewall.


It appears to be working now.

Bob



-Original Message- 
From: Bill Moran

Sent: Monday, January 10, 2011 5:55 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Remote Connection

In response to Bob Pawley rjpaw...@shaw.ca:


Hi

I am attempting to connect from two local interfaces to a remote database.

With one interface (SharpMap developed in C#) I have no problems.

With the other interface (Delphi) I have no problem connecting in design 
mode.


However when I compile Delphi it just hangs, until timeout, without 
opening.


The postgresql log follows. Can someone please interpret  it for me?

Bob

2011-01-07 09:03:55 PSTERROR:  unrecognized configuration parameter 
ssl_renegotiation_limit

2011-01-07 09:03:55 PSTSTATEMENT:  SET ssl_renegotiation_limit=0
2011-01-07 09:04:08 PSTLOG:  could not receive data from client: No 
connection could be made because the target machine actively refused it.



2011-01-07 09:04:08 PSTLOG:  unexpected EOF on client connection
2011-01-07 09:22:58 PSTLOG:  could not receive data from client: No 
connection could be made because the target machine actively refused it.



2011-01-07 09:22:58 PSTLOG:  unexpected EOF on client connection


What version of PostgreSQL are you using and what version does Delphi
think you're using?  It seems to me that the Delphi IDE is connecting
differently than the app it compiles for you, and that said app is
trying to set a configuration parameter that doesn't exist, then aborting
when that fails.

Can't imagine what version you'd be using ... that option seems to have
been around since 8.0 at least.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Remote Connection

[Bob Pawley]

Hi 

I am attempting to connect from two local interfaces to a remote database.

With one interface (SharpMap developed in C#) I have no problems.

With the other interface (Delphi) I have no problem connecting in design mode.

However when I compile Delphi it just hangs, until timeout, without opening.

The postgresql log follows. Can someone please interpret  it for me?

Bob

2011-01-07 09:03:55 PSTERROR:  unrecognized configuration parameter 
ssl_renegotiation_limit
2011-01-07 09:03:55 PSTSTATEMENT:  SET ssl_renegotiation_limit=0
2011-01-07 09:04:08 PSTLOG:  could not receive data from client: No connection 
could be made because the target machine actively refused it.


2011-01-07 09:04:08 PSTLOG:  unexpected EOF on client connection
2011-01-07 09:22:58 PSTLOG:  could not receive data from client: No connection 
could be made because the target machine actively refused it.


2011-01-07 09:22:58 PSTLOG:  unexpected EOF on client connection

Re: [GENERAL] Restore problem

[Bob Pawley]

-Original Message- 
From: Alban Hertroys

Sent: Wednesday, December 29, 2010 4:03 AM
To: Bob Pawley
Cc: Adrian Klaver ; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On 29 Dec 2010, at 4:40, Bob Pawley wrote:
It seems that this has affected just  the triggers - although that is 
quite massive I will just plug away at it until it's done



(Gosh, those lines were hard to find!)

How did you create those functions? With notepad, or from within pgadmin? If 
you look at the function bodies as they are in the database, are their 
line-endings correct?

It's possible that the error occurred as early as that.

Alban Hertroys

The code example I sent has been dumped and restored numerous times and yes 
it was created in PGAdmin.


This dump was from version 8.3 if that means anything.

Bob

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:1208,4d1b2395802657602216958!


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Restore problem

[Bob Pawley]

-Original Message- 
From: Adrian Klaver

Sent: Wednesday, December 29, 2010 8:08 AM
To: pgsql-general@postgresql.org
Cc: Leif Biberg Kristensen
Subject: Re: [GENERAL] Restore problem

On Wednesday 29 December 2010 4:34:39 am Leif Biberg Kristensen wrote:

On Wednesday 29. December 2010 13.18.40 Alban Hertroys wrote:
 Learning Vim is probably time well-spent, but until you do it's

probably not that good a tool for fixing your problem.

 Although Vim is indeed a very powerful editor, it's not particularly

easy to use. Unlike your usual editors like Notepad and friends, it's a
command-based editor, meaning you have to execute a command before you
can input or change data. It's an entirely different paradigm than what
you're probably used to (I may assume wrongly here).

Back when I used Windows, my favorite editor was EditPlus
(http://www.editplus.com/). It isn't free, but well worth the 35 bucks.

As a rather casual coder, I'm very satisfied with the simple editor
Kwrite in KDE. It's a sheer delight compared to Notepad.

regards, Leif


Another choice is Jedit(http://jedit.org/). It is written in Java so you 
will

need that installed. It has a graphical interface so the learning curve is
short.

JEdit shows that numerous ends of line are missing.

I suppose manual recover is the only possibility??

Other than PostgreSQL version 8.3, the only other change from previous dumps 
(Win XP) is my Windows 7 edition.


I know I have been having problems with firewall permissions in Win 7 during 
install and uninstall of PostgreSQL.


Bob

--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Restore problem

[Bob Pawley]

Hi

I have restored a database using psql to windows version 8.4.

During the restore the trigger code became jumbled. 

I now have a great number of lines that have moved so that they are now 
included in  lines the have been commented out – not to mention that the code 
is hard to read.

Is there some way of correcting this – or re restoring the database, so that I 
don’t have to go through the whole code line by line?

Bob

Re: [GENERAL] Restore problem

[Bob Pawley]

-Original Message- 
From: Adrian Klaver

Sent: Tuesday, December 28, 2010 4:21 PM
To: pgsql-general@postgresql.org
Cc: Bob Pawley
Subject: Re: [GENERAL] Restore problem

On Tuesday 28 December 2010 3:06:40 pm Bob Pawley wrote:

Hi

I have restored a database using psql to windows version 8.4.

During the restore the trigger code became jumbled.

I now have a great number of lines that have moved so that they are now
included in  lines the have been commented out – not to mention that the
code is hard to read.


This is in the plain text dump file right?



Is there some way of correcting this – or re restoring the database, so
that I don’t have to go through the whole code line by line?


With out seeing an example that is going to be difficult :)



Bob




--
Adrian Klaver
adrian.kla...@gmail.com

This is the plain text dump file through pg_admin dump. But the plain text 
dump fie through psql restored in the same way.


I don't have a copy of what it was but here is something quite similar to 
the style I had before the dump.-



Begin Drop table if exists size ;
Drop table if exists temp_ ;

Drop table if exists temp1 ;

Drop table  if exists target;

Create table size
( pro_id int4 ,
P_1 float,
P_2 float,
factor float
) ;


create table temp_
( pro_id int4 ,
graphic_id int4 ,
the_geom geometry,
ithe_geom geometry,
othe_geom geometry,
mthe_geom geometry,

ethe_geom geometry,
ip_target geometry,
op_target geometry
);

create table temp1
( id serial unique,
pro_id int4 ,
graphic_id int4 ,
the_geom geometry,
ithe_geom geometry,
othe_geom geometry,
mthe_geom geometry,
ethe_geom geometry,
ip_target geometry,
op_target geometry,
One varchar (5),
Two varchar (5),
Three varchar (5),
Four varchar (5)
);


Following is what it is now. Keep in mind email has word wrap.
(Note -1 is a comment out that, without word wrap, comments out a 
long line of code.


 DECLAREprocess_total integer ;processid integer ; 
procgraphic cursor for select p_id.p_id.process_id


   from  p_id.p_id, processes_countwhere p_id.p_id.p_id_id = 
processes_count.p_id_id


   order by p_id.p_id.process_id;beginSelect count 
(p_id.p_id.process_id) INTO process_totalFROM p_id.p_id, 
processes_count  Where p_id.p_id.p_id_id = 
cesses_count.p_id_id;--1If process_total = 1 
ThenOpen procgraphic; Fetch first from procgraphic 
into processid;




Insert into target (process_id) values (processid) ;

Update p_id.p_idset proc_graphic_position = '1' 
where p_id.p_id.process_id = processid;




   Update p_id.p_id

   set process_number = '1'

   where p_id.p_id.process_id = processid;

   Insert into size (P_1, P_2, pro_id)select 
ST_area(st_envelope (graphics.spatial_ref.the_geom)), ST_area(st_envelope( 
library.dgm_process.the_geom)),( processid) from graphics.spatial_ref, 
library.dgm_process, p_id.p_id, processes_countwhere 
graphics.spatial_ref.position_ = p_id.p_id.proc_graphic_position and 
p_id.p_id.process_id = processidand p_id.p_id.p_id_id = 
processes_count.p_id_idand library.dgm_process.process_number = 
p_id.p_id.process_graphic_id;Update sizeSet factor = 
sqrt(P_1) / sqrt (P_2) / 3.0where size.pro_id = processid; Insert 
into temp_(the_geom, ithe_geom, othe_geom, mthe_geom, ethe_geom, ip_target, 
op_target, pro_id, graphic_id)Select st_scale 
(library.dgm_process.the_geom, size.factor, size.factor),st_scale 
(library.dgm_process.ithe_geom, size.factor, size.factor),st_scale 
(library.dgm_process.othe_geom, size.factor, size.factor),st_scale 
(library.dgm_process.mthe_geom, size.factor, size.factor),


   st_scale (library.dgm_process.ethe_geom, size.factor, size.factor), 
st_scale (library.dgm_process.ip_target, size.factor, size.factor), 
st_scale (library.dgm_process.op_target, size.factor, size.factor), 
(processid), (p_id.p_id.process_graphic_id)from library.dgm_process, 
graphics.spatial_ref, size, p_id.p_idWhere 
graphics.spatial_ref.position_ = p_id.p_id.proc_graphic_positionand 
p_id.p_id.process_id = size.pro_idand size.pro_id = processidand 
library.dgm_process.process_number = p_id.p_id.process_graphic_id;insert 
into temp1 (the_geom, ithe_geom, othe_geom, mthe_geom, ethe_geom, ip_target, 
op_target, pro_id, graphic_id)select st_translate (temp_.the_geom, 
st_x (st_centroid(graphics.spatial_ref.the_geom)) - st_x (st_centroid 
(temp_.the_geom)),st_y (st_centroid(graphics.spatial_ref.the_geom))- 
st_y (st_centroid (temp_.the_geom))),st_translate (temp_.ithe_geom, 
st_x (st_centroid(graphics.spatial_ref.the_geom)) - st_x (st_centroid 
(temp_.the_geom)),st_y (st_centroid(graphics.spatial_ref.the_geom))- 
st_y (st_centroid (temp_.the_geom))),st_translate (temp_.othe_geom, 
st_x (st_centroid(graphics.spatial_ref.the_geom)) - st_x

Re: [GENERAL] Restore problem

[Bob Pawley]

Yes I was just looking at it.

It seems that it was dumped in that form.

Any thoughts on how that could happen?? Not that it will help in this 
instance.


Bob

-Original Message- 
From: Adrian Klaver

Sent: Tuesday, December 28, 2010 6:09 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On Tuesday 28 December 2010 5:58:51 pm Bob Pawley wrote:

-Original Message-
From: Adrian Klaver
Sent: Tuesday, December 28, 2010 4:21 PM
To: pgsql-general@postgresql.org
Cc: Bob Pawley
Subject: Re: [GENERAL] Restore problem

On Tuesday 28 December 2010 3:06:40 pm Bob Pawley wrote:
 Hi

 I have restored a database using psql to windows version 8.4.

 During the restore the trigger code became jumbled.

 I now have a great number of lines that have moved so that they are now
 included in  lines the have been commented out – not to mention that the
 code is hard to read.

This is in the plain text dump file right?

 Is there some way of correcting this – or re restoring the database, so
 that I don’t have to go through the whole code line by line?

With out seeing an example that is going to be difficult :)

 Bob

--
Adrian Klaver
adrian.kla...@gmail.com

This is the plain text dump file through pg_admin dump. But the plain text
dump fie through psql restored in the same way.


I am not following. psql cannot create a dump file. It can however be used 
to
restore a plain text dump file created by pg_restore. I think you are going 
to

need show the steps you took.



I don't have a copy of what it was but here is something quite similar to
the style I had before the dump.-



The restore process does not destroy the input file, it should still be
available.


--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Restore problem

[Bob Pawley]

-Original Message- 
From: Adrian Klaver

Sent: Tuesday, December 28, 2010 6:09 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On Tuesday 28 December 2010 5:58:51 pm Bob Pawley wrote:

-Original Message-
From: Adrian Klaver
Sent: Tuesday, December 28, 2010 4:21 PM
To: pgsql-general@postgresql.org
Cc: Bob Pawley
Subject: Re: [GENERAL] Restore problem

On Tuesday 28 December 2010 3:06:40 pm Bob Pawley wrote:
 Hi

 I have restored a database using psql to windows version 8.4.

 During the restore the trigger code became jumbled.

 I now have a great number of lines that have moved so that they are now
 included in  lines the have been commented out – not to mention that the
 code is hard to read.

This is in the plain text dump file right?

 Is there some way of correcting this – or re restoring the database, so
 that I don’t have to go through the whole code line by line?

With out seeing an example that is going to be difficult :)

 Bob

--
Adrian Klaver
adrian.kla...@gmail.com

This is the plain text dump file through pg_admin dump. But the plain text
dump fie through psql restored in the same way.


I am not following. psql cannot create a dump file. It can however be used 
to
restore a plain text dump file created by pg_restore. I think you are going 
to

need show the steps you took.

I used PGAdmin to dump the June version and pg_dump mydb  db.sql to dump 
the May version.


Both came out with the same problems.

Bob



I don't have a copy of what it was but here is something quite similar to
the style I had before the dump.-



The restore process does not destroy the input file, it should still be
available.


--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Restore problem

[Bob Pawley]

-Original Message- 
From: Adrian Klaver

Sent: Tuesday, December 28, 2010 6:51 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On Tuesday 28 December 2010 6:41:51 pm Bob Pawley wrote:


  Bob

 --
 Adrian Klaver
 adrian.kla...@gmail.com

 This is the plain text dump file through pg_admin dump. But the plain
 text dump fie through psql restored in the same way.

I am not following. psql cannot create a dump file. It can however be used
to
restore a plain text dump file created by pg_restore. I think you are 
going

to
need show the steps you took.

I used PGAdmin to dump the June version and pg_dump mydb  db.sql to dump
the May version.

Both came out with the same problems.

Bob


What program are you using to look at the plain text file?


Notepad

Bob

--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Restore problem

[Bob Pawley]

-Original Message- 
From: Adrian Klaver

Sent: Tuesday, December 28, 2010 7:06 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On 12/28/2010 07:05 PM, Bob Pawley wrote:



-Original Message- From: Adrian Klaver
Sent: Tuesday, December 28, 2010 6:51 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On Tuesday 28 December 2010 6:41:51 pm Bob Pawley wrote:


  Bob

 --
 Adrian Klaver
 adrian.kla...@gmail.com

 This is the plain text dump file through pg_admin dump. But the plain
 text dump fie through psql restored in the same way.

I am not following. psql cannot create a dump file. It can however be
used
to
restore a plain text dump file created by pg_restore. I think you are
going
to
need show the steps you took.

I used PGAdmin to dump the June version and pg_dump mydb  db.sql to dump
the May version.

Both came out with the same problems.

Bob


What program are you using to look at the plain text file?


Notepad

Bob



Open the file in Wordpad and see if it looks better.

It looks the same.

Bob
--
Adrian Klaver
adrian.kla...@gmail.com 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Restore problem

[Bob Pawley]

-Original Message- 
From: Adrian Klaver

Sent: Tuesday, December 28, 2010 7:06 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On 12/28/2010 07:05 PM, Bob Pawley wrote:



-Original Message- From: Adrian Klaver
Sent: Tuesday, December 28, 2010 6:51 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On Tuesday 28 December 2010 6:41:51 pm Bob Pawley wrote:


  Bob

 --
 Adrian Klaver
 adrian.kla...@gmail.com

 This is the plain text dump file through pg_admin dump. But the plain
 text dump fie through psql restored in the same way.

I am not following. psql cannot create a dump file. It can however be
used
to
restore a plain text dump file created by pg_restore. I think you are
going
to
need show the steps you took.

I used PGAdmin to dump the June version and pg_dump mydb  db.sql to dump
the May version.

Both came out with the same problems.

Bob


What program are you using to look at the plain text file?


Notepad

Bob



Open the file in Wordpad and see if it looks better.

I downloaded an sql editor and it looks the same in it as well.

At least the editor will make it easier to fix the problem. However I would 
like to know what happened so I can avoid it in the future.


Is the compressed file a better way to dump??

Bob
--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Restore problem

[Bob Pawley]

-Original Message- 
From: Adrian Klaver

Sent: Tuesday, December 28, 2010 7:33 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On 12/28/2010 07:27 PM, Bob Pawley wrote:



-Original Message- From: Adrian Klaver
Sent: Tuesday, December 28, 2010 7:06 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On 12/28/2010 07:05 PM, Bob Pawley wrote:



-Original Message- From: Adrian Klaver
Sent: Tuesday, December 28, 2010 6:51 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On Tuesday 28 December 2010 6:41:51 pm Bob Pawley wrote:


  Bob

 --
 Adrian Klaver
 adrian.kla...@gmail.com

 This is the plain text dump file through pg_admin dump. But the plain
 text dump fie through psql restored in the same way.

I am not following. psql cannot create a dump file. It can however be
used
to
restore a plain text dump file created by pg_restore. I think you are
going
to
need show the steps you took.

I used PGAdmin to dump the June version and pg_dump mydb  db.sql to
dump
the May version.

Both came out with the same problems.

Bob


What program are you using to look at the plain text file?


Notepad

Bob



Open the file in Wordpad and see if it looks better.

I downloaded an sql editor and it looks the same in it as well.

At least the editor will make it easier to fix the problem. However I
would like to know what happened so I can avoid it in the future.


I am not sure. If the file is not to big and you wish you can send it to
me off list and maybe I can figure out what is going on.

The file is over 9 meg - way to large for me to e-mail.

It seems that this has affected just  the triggers - although that is quite 
massive I will just plug away at it until it's done


Thanks

Bob



Is the compressed file a better way to dump??


Yes in this case because you can do a restore from within pgAdmin.


Bob



--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Restore problem

[Bob Pawley]

-Original Message- 
From: Alan Hodgson

Sent: Tuesday, December 28, 2010 8:12 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On December 28, 2010, Adrian Klaver adrian.kla...@gmail.com wrote:

On 12/28/2010 07:40 PM, Bob Pawley wrote:
 Open the file in Wordpad and see if it looks better.

 I downloaded an sql editor and it looks the same in it as well.

 At least the editor will make it easier to fix the problem. However I
 would like to know what happened so I can avoid it in the future.



It's often a good idea to maintain function definitions outside the 
database,

under version control, and apply them to the database from there.

I would appreciate a more detailed explanation of this.

Bob

Also, try a unix2dos utility on the text of the functions before giving up
and hand editing them.

I'll look at that - I'm also looking at something called Vim
http://www.vim.org/download.php

Bob


--
A hybrid Escalade is missing the point much in the same way that having a
diet soda with your extra large pepperoni pizza is missing the point.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Restore

[Bob Pawley]

Hi

I am attempting to restore a database using -

psql PDW  PDW_June_10.sql

psql –U postgres PDW  PDW_June_10.sql

The response asks me for a password. 

I use the same password with which I connect to the server but it is not 
accepted.

Without the –U postgres identifier it asks me for the password of my computer - 
which doesn’t exist.

How can I get around this??

Bob