from:"Bob"

Re: [GENERAL] Rekall for Free

2005-05-11 Thread Bob

I can't wait!
On 5/11/05, John Dean <[EMAIL PROTECTED]> wrote:
HiThis is my first post to this mailing list. I would like all list membersthat I am the same person who used to work for MySQL AB. Even though I
worked for MySQL AB, PostgreSQL has always been my RDBMS of choice. Thereason for telling you all about about my involvement with MySQL is becauseI do not want anybody to think that I have joined this mailing list to be
disruptive or the sing the praises of MySQL. On the contrary, I have joinedthis list to help users get the best out of PG and to answer questionsrelating to PG/Rekall issues.The first point I would like to raise, relates to something I came across
in the archive, with the subject line "free WINDOWS rekall". Shortly,before Chris Green posted his reply to the original post made by[EMAIL PROTECTED], Mike Richardson( Rekall GPL at
www.rekallrevealed.org) and myself ( Rekall commercial binary distros at www.totalrekall.co.uk )made the decision to give away old versions of Rekall. All Rekall
V2.1.1releases are now available for free download. The only stipulation is thatusers register with the TotalRekall web site. The reason I ask this isbecause occasionally I will send a bulk email notifying everybody of new
releases ( free, GPL and commercial ) . I do this may be 3 or 4 times per year.The second point I would like to raise, concerns our relationship withtheKompany.com (TKC). The simple answer to this point is that we have no
relationship with TKC. At the time of the parting of the ways we made anannouncement to the affect that we would offer Rekall users, who had boughtRekall from TKC, the opportunity to switch over to us for free. This offer
was limited to 12 months, which has since expired. We said at the time thatwe would not provide support or maintenance to TKC customers unless theyswitched over to us. TKC still continue to sell Rekall V2.1.x, which we
offer as a free download, and since Shawn Gordon has very limitedtechnically ability, he will not be able to provide much in the way oftechnical support. He certainly will not be able to provide maintenance
releases, or new releases. TKC is selling an old out of date release for$69, whereas we sell Rekall for £25 ( approx $45 ). We are thinking aboutoffering 3 editions - Standard, Professional and Enterprise. I will say
more about that once we have decided how best to do this and how we much weare going to charge. What I can say is that we are working on some prettycool enhancements to Rekall, which will allow us to split the basic package
into 3 separate editions. The one enhancement which excite us most (at themoment) is Rekall/WEB. If you want to know more about Rekall/WEB take alook at the FAQs on http://www.totalrekall.co.uk
.One thing we have been thinking about for a long time is offering ready touse Rekall applications, which we will sell at a fraction of the price ofsimilar types of applications. In addition, providing there is sufficient
interest we would like to produce a totally PG oriented version of Rekall.If we did that we could include all the administrative feature of PG andlots more---RegardsJohn Dean,co-author of Rekall,
the only alternativeto MS Access---(end of broadcast)---TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [GENERAL] Audit trail ?

2005-05-29 Thread Bob

Sorry for short message, but I'm headed out for the weekend.

At my places of work we have use both a single table and one table for every table.

I personally liked the single table for every table approach. On 5/29/05, Zlatko Matic <[EMAIL PROTECTED]
> wrote:Hello.I must have audit trail of all insert/update/delete on several table. I have
several questions regarding that:1. Is it better to have one audit trail table that collectsinsert/update/delete of all audited tables, or it is better to have separateaudit trail table for every audited table ?
2. To use triggers or rules ? Example for both ?3. Could someone give me an example of a successfull audit trail solution ?I'm running on lack of time, so any help would be precious...Thanks.
---(end of broadcast)---TIP 6: Have you searched our list archives?   http://archives.postgresql.org

Re: [GENERAL] postgresql books

2005-06-03 Thread Bob

I think it hits the press in June or July 2005???
On 6/3/05, Brad Nicholson <[EMAIL PROTECTED]> wrote:
Gevik babakhani wrote:> Dear All, Beside the documentation, which pg book would you recommend? Which one
> is your personal favorite pg book? Regards>> Gevik.>>>PostgreSQL by Korry Douglas and Susan Douglas is an excellent book, buta little out of date.  I beleive a new edition is in the works.
--Brad Nicholson  416-673-4106Database Administrator, Afilias Canada Corp.---(end of broadcast)---TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] writting a large store procedure

2005-06-03 Thread Bob

Well first off can you break those 700 lines out into more stored functions? 
 
Maybe you have logic that would be best in its own function and than call one function from another. Makes testing many times easier.  I always think in small chuncks when I write code.
 
Once code gets to be more than a few pages it can become hell to debug and work with.  Plus breaking it out might allow you to use that same logic in other parts of your system because it will be stand alone piece of logic/code.
 
On 6/2/05, "Rodríguez Rodríguez, Pere" <[EMAIL PROTECTED]> wrote:

Hello, 
I'm writing a large store procedures (more than 700 lines) and I have much problems to debug it. How can I debug it easily?
I use pgAdmin, is there another editor (free software) that permit write large store procedure more easily? 
Thanks in advance. 
pere

Re: [GENERAL] writting a large store procedure

2005-06-03 Thread Bob

Well first off can you break those 700 lines out into more stored functions? 
 
Maybe you have logic that would be best in its own function and than call one function from another. Makes testing many times easier.  I always think in small chuncks when I write code.
 
Once code gets to be more than a few pages it can become hell to debug and work with.  Plus breaking it out might allow you to use that same logic in other parts of your system because it will be stand alone piece of logic/code.
 
On 6/2/05, "Rodríguez Rodríguez, Pere" <[EMAIL PROTECTED]> wrote:

Hello, 
I'm writing a large store procedures (more than 700 lines) and I have much problems to debug it. How can I debug it easily?
I use pgAdmin, is there another editor (free software) that permit write large store procedure more easily? 
Thanks in advance. 
pere

[GENERAL] pl/pgsql list

2005-06-07 Thread Bob

What are the opinions on a separate list just for pl/pgsql?  Seems pl/pgsql deserves her own area. Just wondering if this would make sense, and if it did can we have a separate list?

Re: [GENERAL] pl/pgsql list

2005-06-07 Thread Bob

My thought is on it's own pl/pgsql is just as important as straight SQL. Maybe as time goes on we will see higher volumes of pl/pgsql questions, if that is what warrants a separate list.  I personally don't see why one would put pl/pgsql in with everything else. Maybe because I come from an Oracle world where volumes of books have been written on PL/SQL on it's own.

 
Bob 
On 6/7/05, Douglas McNaught <[EMAIL PROTECTED]> wrote:
Bob <[EMAIL PROTECTED]> writes:> What are the opinions on a separate list just for pl/pgsql?  Seems
> pl/pgsql deserves her own area. Just wondering if this would make sense,> and if it did can we have a separate list?I don't think it makes sense--it's not like traffic related topl/pgsql floods out everything else on the list.  For better or worse,
it's an integral part of PG--what's the rationale for a separate list?-Doug

Re: [GENERAL] Debugging PL/pgSQL

2005-06-21 Thread Bob

Keep in mind there is no built in API to debug PL/pgSQL like there is for PL/SQL. You will have to use the old true and tried output statements to debug your stored procs.
On 6/21/05, Sean Davis <[EMAIL PROTECTED]> wrote:
You might want to look at pgEdit.Sean- Original Message -From: "Craig Bryden" <
[EMAIL PROTECTED]>To: "pgsql" Sent: Tuesday, June 21, 2005 3:01 PM
Subject: [GENERAL] Debugging PL/pgSQL> Hi>> Does anyone know of a free SQL Editor that allows you to debug PL/pgSQL> functions?>> Thanks> Craig>>
> ---(end of broadcast)---> TIP 6: Have you searched our list archives?>>   http://archives.postgresql.org
>---(end of broadcast)---TIP 2: you can get off all lists at once with the unregister command   (send "unregister YourEmailAddressHere" to 
[EMAIL PROTECTED])

Re: [GENERAL] Possible move away from PG

2005-06-30 Thread Bob

PL/SQL is awesome just awesome no doubt about it. 
 
But if you like to pay at a minium for 5 users 800 bucks then stick with PL/SQL.  I personally can live with pl/psql and at a higher level postgresql to be honest.  Not to mention the rate that things are being added and improved is mind numbing.  Depends on your setup though. Do you really need all the bells and whistles that PL/SQL gives you given the cost and maintence of Oracle databases.
 
On 6/30/05, Jason Tesser <[EMAIL PROTECTED]> wrote:
I am not familar with how to do this.  Could you give me an example of howthis could help?
>> Have you considered returing refcursors instead of setof some type.>> Kris Jurka--Jason TesserDeveloper for NMI[EMAIL PROTECTED]Eph 2:8-10
---(end of broadcast)---TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] COnsidering a move away from Postgres

2005-07-01 Thread Bob

I have used PL/SQL for years. It's a great language that is easy to pick up and offers lots of ability/promise.  The syntax seems very easy for new people to pick up who might know another language or are just starting out.  Of course the same can be said of Ada code.  It's just very easy to read.

On 7/1/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]
> wrote:
> Out of curiosity, what other backends do you consider and what is their> syntax for such problems.
Most folks that use Oracle's PL/SQL like it.  I have a sneaking suspicionOracle used the GNAT parser for Ada as a starting point, but that is pureconjecture.  Oracle does document that PL/SQL is Ada with SQL extensions.
An enterprising individual could get the source for GNAT from AdaCore (it'sGPL'd) and create a workalike language for PostgreSQL.  I'm notenterprising.Rick
[EMAIL PROTECTED] wrote on 06/30/2005 04:03:49 PM:> On 6/30/05, Jason Tesser <[EMAIL PROTECTED]> wrote:> > I work for a college and we use PG currently as our main backend.  We
are> > currently developing with Java.  We are considering moving away> from postgres> > for the reasons I am going to list below.  I would appreciate somethoughts> > from the Postgres community on way we should or shouldn't leave
postgres.>> Out of curiosity, what other backends do you consider and what is their> syntax for such problems.  Don't get me wrong, I don't intend to prove> anything by asking so.  I am just curious what syntax would you prefer,
> or in other words, what syntax is most convenient for a person doing> procedural language intense project.  Hopefully it will help PL/pgSQL> develop in a best direction.>> So, please post samples of syntax (and a DB-name, I'm curious about
> other DBs syntaxes).>>Regards,>  Dawid>> ---(end of broadcast)---> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Client-Server Example

2005-07-13 Thread Bob

Have you checked your pg_hba.conf file and add the correct entry to
allow the remote client ip address? Sorry don't have my file so I can't
show you an example. But I think the docs and/or a google will show
some examples.

Also you have to alter the postgresql.conf to allow tcp/ip connections.
I think you can do this at the command line to. But maybe you have to
do it in both the file and the command prompt?? Not sure as I'm on
windows and it may be different then linux.On 7/13/05, John Tulodziecki <[EMAIL PROTECTED]
> wrote:

Hi there

I am trying to run a simple client application on one linux
box (running redhat 9) whilst the server (i.e postmaster) is running on another
box.

I have successfully run the client program locally on the
server machine.

I am getting connection refused when I try a remote
connection

I have been through all the required settings in the conf
files and I cannot see the error.

Can anyone please give me a simple example of a client
program (please include required conf file settings)

which is communicating with a server program (please include
required conf file settings) on another machine.

Am I right in saying that the postmaster process must only
be running on the server machine ?

I have checked that the postmaster account is running on the
server machine and is not running on the client

Machine.

Thank you.

John Tulodziecki

Senior Software Engineer
Squire Technologies Ltd

Phone +44(0)1305 757315
Web www.squire-technologies.com
Email [EMAIL PROTECTED]

NOTICE AND DISCLAIMER:

Any views expressed in this message are those of the
individual sender,
except where the sender specifically states them to be the views of Squire
Technologies Ltd. If you have received this email in error please notify the
sender immediately and delete this email from your system without copying or
disseminating it or placing any reliance upon its contents.

Re: [GENERAL] Transparent encryption in PostgreSQL?

2005-07-13 Thread Bob

Doesn't that really only save you from having someone come in at the OS
level and copying your data files and than moutning them on a differet
server/database.  A person could still come in to psql as a dba or
anyone for that matter with the proper select grants and query off that
data and see it in encrypted.  

Not that this helps here but Oracle just implemented row level
encryption in 10g release 2. In simple form everything has a key and
for you to view the data from anywhere including sql plus(it's like
psql) you need the correct key to decrypt it(I'm pulling this from my
head after haveing read this some time ago).  This all happens on
the fly.  Of course there will be performance hits for this but
for today's world where the weakest link is usually an internal
employee with access to all the data  the only way to keep people
from seeing it is a setup that encrypts it at the cost of
performance.  Maybe the Oracle method is something that can make
it's way to Postgresql over time. If there isn't a third party patch
that already does this.On 7/13/05, Joshua D. Drake <[EMAIL PROTECTED]> wrote:
> My sense is that this is a difficult problem.  However, I made the> mistake of promising this functionality,Well it isn't that difficult except that you need some level of two wayencryption and it is going to be a performance nightmare.
I would suggest instead just mounting postgresql on an encrypted filesystem.Sincerely,Joshua D. Drake> so I'm scrambling to figure out some kind of solution.  Any> suggestions?
>> Thanks so much!>> Matt--Your PostgreSQL solutions provider, Command Prompt, Inc.24x7 support - 1.800.492.2240, programming, and consultingHome of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit
http://www.commandprompt.com / http://www.postgresql.org---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] To Postgres or not

2005-07-13 Thread Bob

Even though PostgreSQL is more like an Oracle which is a good thing.
It's like Oracle in function not in cost and adminstration. PostgreSQL
not only fits into the enterprice really well it also fits in the mom
and pop shops(dentist office,corner store, you name it that may not
have any IT folks.  So you get lots of the Oracle like bell and
whistles while not having the Oracle weight(admin,cost,complexity,etc)
that only a larger company can hold. Oracle claims to have a small
business version of the 10g database. But the docs say it's for small
to mid sized "enterprises" not Joe Blow on the corner shop or even Joe
Blow and his 100 employees selling widgets from their basement.

So my opinion in 99% of cases is why not use PostgreSQL over MySQL and
in many cases why not use PostgreSQL over Oracle.  What does MySQL
really offer you?  Go ahead and tell me it can query faster with
one user logged in much faster. If that is the case use flat files why
waste your time on a relational database. Anyways I could go on and on,
I'll stop now and leave it at this.  PostgreSQL is great piece of
software that we should all be great full is out there for us to use!

PostgreSQL has in a nutshell:
Great documentation
Great community
Great features
Great cost(free)On 7/13/05, David Fetter <[EMAIL PROTECTED]> wrote:
On Tue, Jul 12, 2005 at 05:16:44PM +, Ted Slate wrote:> Hello Everyone,>> I considering moving a product to Solaris/Linux and trying to find a> good DB.  Oracle is out due to cost so as far as i know the only
> reasonable alternatives are Postgres or Codebase or MySQL.  Does> anyone here have any experience using Codebase or MySql?  If I stick> with a true RDBMS then Codebase is out.  So that leaves Postgres and
> MySQL.  I'm very used to all the comforts of Oracle so I think> Postgres stacks up better but maybe some veterans could shed some> light.PostgreSQL is more like an Oracle, DB2, MS-SQL Server, etc. than it is
like a MySQL or a BerkeleyDB.  If your app is more Oraclish thanBerkeleyDBish, PostgreSQL is very likely your choice.> In other words, is Postgres really that much better than MySQL> and/or the other way around?
Here's my experience.  With PostgreSQL, when you reach for a newcapability, it's usually right there, or at worst it's easy toconstruct.  With MySQL, you're constantly running into barriers andhaving to kludge around them.
> Also, in my searches I ran across an company called EnterpriseDB and> another like it.  Basically they offer Postgres support.  So I'm a> little concerned that I'm just robbing Peter to pay Paul.
Many kinds of support are available, and some of the best is free.This mailing list, for example, is excellent, and people on Coremonitor it.   is good, too.  Thereare also paid support options, as you've mentioned.Cheers,D--David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778Remember to vote!---(end of broadcast)---TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Transparent encryption in PostgreSQL?

2005-07-13 Thread Bob

Here is the link in case your fingers are broken and it hurts to type;)
http://www.postgresql.org/docs/8.0/interactive/encryption-options.html
On 7/13/05, Matt McNeil <[EMAIL PROTECTED]> wrote:





Greetings,
 
I need to securely store lots of sensitive contact 
information andnotes in a freely available database (eg PostgreSQL or MySQL) that 
will bestored on a database server which I do not have direct access to. 
This database will be accessed by a PHP application that I 
amdeveloping.  However, I also need to be able to search/sort these 
datawith the database functions (SELECT, ORDER BY, regexes, etc) so encrypting on
the client side (web application) or using 
encryption of specific fields 
would not work.  (For example, I need to encryptcontacts' names, but need to 
be able to search for results by 
name). (Irealize I could load the entire table into memory with PHP 
andprocess/search/sort it there, butthat's obviously not a very good 
solution).  Ideally I would like toencrypt entire 
tables.  I read something about the 
pgcrypto contrib
module, but have't been able to discern if it can do 
ecryption in a
transparent way (e.g. so that I can 
do regex searches on the data).
 
My sense is that this is a difficult problem.  
However, I made themistake of promising this functionality, so I'm 
scrambling to figure out some kind of solution.  
Anysuggestions?
 
Thanks so much!
 
Matt

Re: [GENERAL] Select All Columns

2005-09-01 Thread Bob

The first question is do you really need all the columns. Most times
you don't need them. There will be network overhead for sure in
returning all columns instead of just the few that you want. Also
select * is not very clear with what is going on in the statement. 
Where I work we have standards to always qualify the columns by name
and to not use the *.Another issue I have seen is that if your code
logic assumes that select * returns
column1 column2 column3 and you add a new column to that table your
logic can break.  I often see the select * used when people get lazy. 
To me select * should only be used in ad-hoc fashion when digging
around in tables and such.

Just my 2 cents

umn

On 9/1/05, Tan Chen Yee <[EMAIL PROTECTED]> wrote:
> If I want to retrieve all columns from a table, is there any disadvantage
> by
> using select *
> instead of listing all the columns ? Will select * cause overhead, more
> times to run ?
> 
> Thanks !
> 
> Tan
> 
> 
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
>

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Oracle 10g Express - any danger for Postgres?

2005-11-04 Thread Bob

On 11/1/05, Tom Lane <[EMAIL PROTECTED]> wrote:
> Jan Wieck <[EMAIL PROTECTED]> writes:
> > On 10/31/2005 1:14 PM, Chris Browne wrote:
> >> The fact that it appears "a joke" to people wanting to deploy big
> >> databases doesn't prevent it from taking a painful bite out of, oh,
> >> say, certain vendors that forgot to own their own transactional
> >> storage engine...
>
> > It's not a joke. It fits exactly the "small web application" needs. Who
> > will want to pay for a commercial MySQL license when they can run Oracle
> > for free?
>
> People who can't figure out how to configure Postgres are not likely to
> get far with Oracle ;-).  Unless Oracle has made some *huge* strides in
> ease of installation/administration with 10g, I see this making
> practically no dent in MySQL.  Or PG for that matter.  All they're
> really likely to accomplish is to cannibalize some of their own low-end
> sales.
>
>   regards, tom lane
>
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
>
Well to be fair, Oracle 10g Express is easy to install and admin.
Basically you don't have to do any admin work and installing is as
hard as clicking next 3 or 4 times.
To me the only really nice thing Oracle has at this time is called
HTML DB that provides a semi easy development tool that hooks into
Oracle very easily. No need to write glue code such as connections and
state as the dev tool provides all this.

With that being said those of us who know better will not take that
over Postgresql, but it will buy Oracle more market share that is for
sure.

Bob

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] pl/perl autonomous transactions question

2006-09-25 Thread Bob

I would like to  use autonomous transactions for a large batch process and I want this all encapsulated within stored procedures. I want to commit after say every 15,000 records. The only way I have found to do this is to use the perl DBI in my stored procedure to establish a new connection to the database.
1. Is there any way to tell the DBI connection to use the current credtials just with a new connection?  2. Is there any way to get the spi call to create a new connection instead of using the connection it is called with?
 One issue I see with my current DBI  solution is I need to hard code or pass in as variables the connection information.  I would prefer not to have the password lying around in plain site.  Keep in mind this is a batch process not a something I that is called manually where a user is
 going to be entering their username and password in.Any help or ideas would be great.Below is a simple example to demonstrate. CREATE TABLE test_values ( c1 SERIAL, c2 VARCHAR (200));
CREATE OR REPLACE FUNCTION proc_perl_test_insert() RETURNS VOID AS $$use DBI;my $db_host = 'localhost';my $db_user = 'postgres';my $db_pass = 'somepassword';my $db_name = 'dev';elog(NOTICE,"Executeing proc_perl_test_insert");
#Creates a new connection so that an autonomous transactions can take place independent of main transaction.#INSERT INTO test_values (c2) VALUES ('Autonomous Transaction') will commit regardless if the calling transaction fails or is rolled back.
my $db = "DBI:PgPP:dbname=${db_name};host=${db_host}";my $dbh=DBI->connect("DBI:PgPP:dbname=dev;host=localhost","postgres", "c21993b");  if ($dbh) {  my $sth = $dbh->prepare("INSERT INTO test_values (c2) VALUES ('Autonomous Transaction')");
 $sth->execute(); }#This inserts using spi_exec_query and will only commit if the calling transaction commits. for ($count=1; $count<2; $count++){  my $query = qq{
		INSERT INTO test_values ( c2 )		VALUES ( 'Non Autonomous Transaction' )		};  my $rv = spi_exec_query($query);}$$ LANGUAGE plperlu;--Now Test the pl/perl function from psql and use a outer transaction
START TRANSACTION;SELECT proc_perl_test_insert(); ROLLBACK TRANSACTION;SELECT * FROM test_values;--HERE IS MY psql commands being run as you can see it does what I want in that it commits my one statement but not the other
dev=# START TRANSACTION;START TRANSACTIONTime: 0.000 msdev=# SElECT * FROM proc_perl_test_insert();NOTICE:  Running proc_perl_test_insert proc_perl_test_insert---(1 row)
Time: 70.000 msdev=# ROLLBACK TRANSACTION;ROLLBACKTime: 0.000 msdev=# select * from test_values;   c1|   c2-+ 2898364 | Autonomous Transaction
(1 row)Time: 0.000 msdev=#Regards,Bob Henkel

Re: [GENERAL] continued segmentation fault

2006-09-27 Thread Bob

Is there any reason can't update to a newer version. Like 8.x?
Geoffrey wrote:
> We continue to have segmentation faults of the /usr/bin/postgres process
>   as I mentioned in an earlier thread.  In all cases, the core file
> always indicates a segmentation fault, but the backtraces don't seem to
> consistently point to any particular problem.  Then again, when you go
> stomping around in memory where you don't belong, all bets are probably off.
>
> I'm wondering out loud (here) if anyone thinks it might have something
> to do with the version we're on?  7.4.7.  We are planning to upgrade to
> 7.4.13 soon, but were hoping to address this existing issue first.
>
> It's a bit difficult debugging this issue as I must initiate gdb via the
> client.  I have yet to reproduce this problem in my test environment,
> but then again, I'm running my single debugging client, whereas in the
> production system, there could well be 150-200 clients running.
>
> I've attached the latest backtrace, in the event anyone sees anything
> glaringly obvious, please slap me in the head...
>
> Thanks in advance.
>
> --
> Until later, Geoffrey
>
> Those who would give up essential Liberty, to purchase a little
> temporary Safety, deserve neither Liberty nor Safety.
>   - Benjamin Franklin
>
> --020607020903010406060907
> Content-Type: text/plain; charset=utf-8
> Content-Disposition: inline;
>   filename="bt1031"
> X-Google-AttachSize: 5397
>
> Using host libthread_db library "/lib/tls/libthread_db.so.1".
> Core was generated by `postgres: chuck sev [local] SELECT'.
> Program terminated with signal 11, Segmentation fault.
> #0  0x0815c973 in cmtreefree (cm=0x9925064, tree=0x0, level=2)
> at regc_color.c:125
>   in regc_color.c
> #0  0x0815c973 in cmtreefree (cm=0x9925064, tree=0x0, level=2)
> at regc_color.c:125
> #1  0x0815c9bc in cmtreefree (cm=0x9925064, tree=0x98a1528, level=1)
> at regc_color.c:131
> #2  0x0815c9bc in cmtreefree (cm=0x9925064, tree=0x9925144, level=0)
> at regc_color.c:131
> #3  0x0815c8f1 in freecm (cm=0x9925064) at regc_color.c:97
> #4  0x0815b458 in rfree (re=0x800) at regcomp.c:2105
> #5  0x08162825 in pg_regfree (re=0x9925064) at regfree.c:53
> #6  0x081aa8ad in RE_compile_and_execute (text_re=0x9f171a0,
> dat=0x800 , dat_len=170796744, cflags=11,
> nmatch=0, pmatch=0x0) at regexp.c:212
> #7  0x081aafd2 in texticregexne (fcinfo=0xfeffac80) at regexp.c:387
> #8  0x08107634 in ExecMakeFunctionResult (fcache=0x9f54ff0,
> econtext=0x9f548e8, isNull=0xfeffad9b "", isDone=0x0) at execQual.c:907
> #9  0x081091a0 in ExecEvalExpr (expression=0x9f54ff0, econtext=0x9f548e8,
> isNull=0x0, isDone=0x9925064) at execQual.c:2257
> #10 0x08109dbb in ExecQual (qual=0x9f54b10, econtext=0x9f548e8,
> resultForNull=0 '\0') at execQual.c:2859
> #11 0x0810a1e1 in ExecScan (node=0x9f54860, accessMtd=0x8112150 )
> at execScan.c:129
> #12 0x08112239 in ExecSeqScan (node=0x800) at nodeSeqscan.c:131
> #13 0x081060d6 in ExecProcNode (node=0x9f54860) at execProcnode.c:306
> #14 0x0810e13b in ExecHash (node=0x9f54730) at nodeHash.c:81
> #15 0x081061b5 in ExecProcNode (node=0x9f54730) at execProcnode.c:364
> #16 0x0810ec6c in ExecHashJoin (node=0x942bbc8) at nodeHashjoin.c:128
> #17 0x08106167 in ExecProcNode (node=0x942bbc8) at execProcnode.c:337
> #18 0x0810f03b in ExecHashJoinOuterGetTuple (node=0x800, hjstate=0x942b460)
> at nodeHashjoin.c:510
> #19 0x0810ea94 in ExecHashJoin (node=0x942b460) at nodeHashjoin.c:152
> #20 0x08106167 in ExecProcNode (node=0x942b460) at execProcnode.c:337
> #21 0x0810d288 in agg_fill_hash_table (aggstate=0x942b850) at nodeAgg.c:905
> #22 0x0810cec7 in ExecAgg (node=0x942b850) at nodeAgg.c:654
> #23 0x0810619b in ExecProcNode (node=0x942b850) at execProcnode.c:356
> #24 0x08104a1d in ExecutePlan (estate=0x942b398, planstate=0x942b850,
> operation=CMD_SELECT, numberTuples=10, direction=2048, dest=0x826d134)
> at execMain.c:1100
> #25 0x08103df8 in ExecutorRun (queryDesc=0x9f17848,
> direction=ForwardScanDirection, count=2048) at execMain.c:249
> #26 0x0817b07b in PortalRunSelect (portal=0x9439c98, forward=0 '\0', count=10,
> dest=0x826d134) at pquery.c:590
> #27 0x0817b6d3 in PortalRunFetch (portal=0x9439c98, fdirection=2048,
> count=2048, dest=0x800) at pquery.c:961
> #28 0x08117c67 in _SPI_cursor_operation (portal=0x9439c98, forward=1 '\001',
> count=10, dest=0x826d134) at spi.c:1315
> #29 0x081171eb in SPI_cursor_fetch (portal=0x800, forward=1 '\001', 
> count=2048)
> at spi.c:881
> #30 0x00a0ca39 in exec_stmt_fors (estate=0xfeffb210, stmt=0x950b188)
> at pl_exec.c:1391
> #31 0x00a0c10e in exec_stmt (estate=0xfeffb210, stmt=0x950b188)
> at pl_exec.c:963
> #32 0x00a0c005 in exec_stmts (estate=0xfeffb210, stmts=0x96fc0d8)
> at pl_exec.c:903
> #33 0x00a0be15 in exec_stmt_block (estate=0xfeffb210, block=0x944ba68)
> at pl_exec.c:859
> #34 0x00a0b061 in plpgsql_exec_function (func=0x96069c8,

[GENERAL] Subqueries failing inside pl/pgsql fuction called by trigger

2004-04-07 Thread Bob

Hi,

I have a very odd postgresql problem.

I have some subqueries contained within a function which are looking
for unrefernced data and then delete any rows that are found:

CREATE OR REPLACE FUNCTION housekeeping()
  RETURNS TRIGGER AS '
  BEGIN
  DELETE FROM properties WHERE NOT EXISTS (SELECT property_id FROM
properties_vw WHERE property_id = properties.property_id);
  DELETE FROM promotions_lookup WHERE promotions_lookup.web_end <
CURRENT_DATE;
  DELETE FROM accommodation_page WHERE NOT EXISTS (SELECT
accommodation_page_id FROM accommodation_page_vw WHERE
accommodation_page.accommodation_page_id = accommodation_page_id);
  DELETE FROM location_page WHERE NOT EXISTS (SELECT location_page_id
FROM location_page_vw WHERE location_page.location_page_id =
location_page_id);
  DELETE FROM dining_page WHERE NOT EXISTS (SELECT dining_page_id FROM
dining_page_vw WHERE dining_page.dining_page_id = dining_page_id);
  DELETE FROM meeting_page WHERE NOT EXISTS (SELECT meeting_page_id
FROM meeting_page_vw WHERE meeting_page.meeting_page_id =
meeting_page_id);
  DELETE FROM meeting_specials WHERE NOT EXISTS (SELECT
meeting_specials_id FROM meeting_specials_vw WHERE
meeting_specials.meeting_specials_id = meeting_specials_id);
  DELETE FROM meeting_rooms WHERE NOT EXISTS (SELECT room_id FROM
meeting_rooms_lookup WHERE meeting_rooms_lookup.room_id =
meeting_rooms.room_id);
  DELETE FROM capacity_data WHERE NOT EXISTS (SELECT data_id FROM
capacities WHERE capacities.data_id = capacity_data.data_id);
  DELETE FROM billboard WHERE NOT EXISTS (SELECT billboard_id from
billboard_vw WHERE billboard.billboard_id = billboard_id);
  DELETE FROM images WHERE NOT EXISTS (SELECT image_id FROM images_vw
WHERE images.image_id = image_id);
  DELETE FROM promotions_lookup WHERE NOT EXISTS (SELECT property_id
FROM properties_vw WHERE property_id = properties.property_id);
  RETURN NULL;
  END; '
  LANGUAGE plpgsql;

I then call the function using this trigger:

CREATE TRIGGER a_delete_brand_trigger
  AFTER DELETE
  ON brands FOR EACH ROW
  EXECUTE PROCEDURE housekeeping();

So I am expecting a query like:

DELETE FROM brands WHERE brand_id = 10;

to fire the trigger which executes function containing the delete
statements and this in turn, removes any unreferenced data.

However, I'm finding that not all of my DELETE statements within the
function are being executed.  If I run the statements outside of the
function they work.  Indeed, if I SELECT housekeeping(); all of the
statements are executed.

Can any body see why when the function is called by the trigger only
some of the statements are executed?

DELETE FROM properties WHERE NOT EXISTS (SELECT property_id FROM
properties_vw WHERE property_id = properties.property_id);
DELETE FROM promotions_lookup WHERE promotions_lookup.web_end <
CURRENT_DATE;

The two above ALWAYS work.  The rest seem to be exectued but they do
not effect any rows.

What's going on?

Many thanks,

Rob.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

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

2015-05-30 Thread 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] PG 9.4.4 issue on French Windows 32 bits

2015-07-08 Thread 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   -c “show client_encoding”
$ psql   -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  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

Re: [GENERAL] Postgresql 9.3.4 file system compatibility

2016-04-08 Thread 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  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] Initdb --data-checksums by default

2016-04-22 Thread Bob Lunney


> On Apr 22, 2016, at 3:21 AM, Stuart Bishop  wrote:
> 
> On 20 April 2016 at 14:43, Alex Ignatov  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 
> 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] Postgres processes getting stuck (bug?)

2016-04-29 Thread 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  
> 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

[GENERAL] Excell

2007-06-19 Thread Bob Pawley

Hi All

Is there a fast and easy method of transferring information between MS Excel 
and PostgreSQL??

Bob Pawley

Re: [GENERAL] Excell

2007-06-19 Thread Bob Pawley


Thanks

Does one version of ODBC work for all versions of Excel and Postgresql.

I am wanting to transfer one or two tables from Excel and manipulate the 
information in Postgresql then transfer the results back to Excel as a 
single table.


I am using Excel 2000 and PostgreSql 8.1.

Bob


- Original Message - 
From: "David Gardner" <[EMAIL PROTECTED]>

To: "Postgresql" 
Sent: Tuesday, June 19, 2007 4:56 PM
Subject: Re: [GENERAL] Excell


Agreed ODBC is the way to go, depending on what you are doing, Access may 
be helpfull as an intermediate step.


Joshua D. Drake wrote:

Bob Pawley wrote:


Hi All
 Is there a fast and easy method of transferring information between MS 
Excel and PostgreSQL??


odbc?

Joshua D. Drake



 Bob Pawley






---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Excell

2007-06-21 Thread Bob Pawley

Yes please send me a copy.

Bob

- Original Message - 
From: "Harvey, Allan AC" <[EMAIL PROTECTED]>
To: "Joshua D. Drake" <[EMAIL PROTECTED]>; "Scott Marlowe" 
<[EMAIL PROTECTED]>
Cc: "Csaba Nagy" <[EMAIL PROTECTED]>; "David Gardner" 
<[EMAIL PROTECTED]>; "Postgres general mailing list" 

Sent: Thursday, June 21, 2007 9:01 PM
Subject: Re: [GENERAL] Excell

> Because I'm delivering reports to dozens of people who have windows, no
> psql client, and just want to go to a web page, click a button, and get
> their report (or was that a banana?)

I do exactly this with bog basic HTML and bash scripts.
Can send you a copy if you want examples.

Allan

The material contained in this email may be confidential, privileged or 
copyrighted. If you are not the intended recipient, use, disclosure or 
copying of this information is prohibited. If you have received this 
document in error, please advise the sender and delete the document. 
Neither OneSteel nor the sender accept responsibility for any viruses 
contained in this email or any attachments.

---(end of broadcast)---
TIP 6: explain analyze is your friend

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[GENERAL] Establishing a primary key

2007-06-22 Thread Bob Pawley

I have numerous entries in a column of table 1, some of which are duplicated.

I need to transfer this information to table 2 so that I have column that can 
be used as a primery key.

Any help is appreciated.

Bob Pawley

[GENERAL] Postgres Geometry

2007-07-16 Thread Bob Pawley

I have developed a PostgreSQL database c/w a Delphi interface with which to 
input data.

I would like to display this data graphically.

Following is a small example of the type of graphic display I am seeking.

Can anyone tell me if Postgres Geo is a suitable vehicle for this application?

If so, are there any tools that may assist me in developing this graphic 
interface?

Bob Pawley

<>

[GENERAL] PG Admin

2007-07-31 Thread Bob Pawley

Can anyone tell me why a table developed through the PG Admin interface isn't 
found by SQL when accessing it through the SQL interface??

Bob Pawley

[GENERAL] pg_dump

2007-10-02 Thread Bob Pawley

I want to be able to convert a PostgreSQL database to other formats such as 
Oracle, Access etc. - with, as well as without, the data.

Can this task be accomplished by employing pg_dump in  SQL?

Bob Pawley

Re: [GENERAL] pg_dump

2007-10-02 Thread Bob Pawley

Is there a better method of transfering the database and data to between 
DBs?


Bob


- Original Message - 
From: "Joshua D. Drake" <[EMAIL PROTECTED]>

To: "Bob Pawley" <[EMAIL PROTECTED]>
Cc: 
Sent: Tuesday, October 02, 2007 3:26 PM
Subject: Re: [GENERAL] pg_dump



-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Bob Pawley wrote:
I want to be able to convert a PostgreSQL database to other formats such 
as Oracle, Access etc. - with, as well as without, the data.


Can this task be accomplished by employing pg_dump in  SQL?


If you dump with inserts, data only, then yes but it will be slow as
snot to import.



Bob Pawley



- --

 === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHAsWQATb/zqfZUUQRAo2nAJ93XacJFk4zNyTjGYVa35TC8WmVKQCfVA76
U2PX23XVDdWkK6E73knPAG0=
=pKtH
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq 



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[GENERAL] Declare Cursor

2007-10-11 Thread Bob Pawley

The documentation states that PostgreSQL does not support updating data via a 
cursor.

Has that changed in the last little while.

Bob Pawley

[GENERAL] ordering rows

2007-10-18 Thread Bob Pawley

Hi

I have a table 'import' which is an amalgam of two other tables 'loops' and 
'devices'.

The ID column of loops is reflected in the import table. However the order of 
rows ends up as, a for instance, 6, 8, 7, 4, 5 3, 2, 1.

I need to fetch these rows one at a time, in sequence with the loop ID, to 
process them in a delphi interface before going on to the next row.

Query-

1. Can I somehow ensure that the rows are transfered to the import table in 
numerical sequence with the ID of the loop table?

2. Is there a method of using the fetch command to ensure that rows are picked 
up sequentially - lowest ID to highest ID or vice-versa?

3. Is there another mehod of accomplishing this task other than fetch?

Bob Pawley

Re: [GENERAL] ordering rows

2007-10-18 Thread Bob Pawley

That's marvelous - thanks.

Bob
- Original Message - 
From: "brian" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, October 18, 2007 9:45 AM
Subject: Re: [GENERAL] ordering rows

Bob Pawley wrote:

Hi

I have a table 'import' which is an amalgam of two other tables
'loops' and 'devices'.

The ID column of loops is reflected in the import table. However the
order of rows ends up as, a for instance, 6, 8, 7, 4, 5 3, 2, 1.

These are the IDs from loops?

I need to fetch these rows one at a time, in sequence with the loop
ID, to process them in a delphi interface before going on to the next
row.

ORDER BY loops.id ASC

Query-

1. Can I somehow ensure that the rows are transfered to the import
table in numerical sequence with the ID of the loop table?

Ensure that *what* is transferred? The rows from imports? Do they have a 
foreign key pointing to loops?

2. Is there a method of using the fetch command to ensure that rows
are picked up sequentially - lowest ID to highest ID or vice-versa?

3. Is there another mehod of accomplishing this task other than
fetch?

Perhaps you should post your SELECT statement and a sample of the result 
you're getting (and a sample of what you desire to get wouldn't hurt).

brian

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[GENERAL] Fetch

2007-10-18 Thread Bob Pawley

When I fetch a row it returns a row number.

Is there a method under the fetch command of either not returning the row 
number or of ignoring it after it is returned.

  begin work; 
  Declare loop_set  Cursor 
   for Select  one, two from loop_import 
   order by loop_id ;
 fetch next From loop_set; 

Bob Pawley

[GENERAL] Temp Table

2007-10-19 Thread Bob Pawley

When I fetch a row, as in the following, how do I copy the row's data into a 
temporary table so that I can process it??

begin work; 
  Declare loop_set  Cursor 
   for Select  one, two, three, four, five, six, seven from loop_import 
   order by loop_id ;
 fetch next From loop_set; 


Bob Pawley

[GENERAL] Select Command

2007-10-21 Thread Bob Pawley

I have a column with data structured as follows.

32TT - 0002
32LT- 0004
32PT-0005

Is there a way of selecting all of the rows containing LT in that column??


I have attempted variations of ' *LT* ' with out success.

Bob Pawley

[GENERAL] PostgreSQL and AutoCad

2007-10-24 Thread Bob Pawley

Is there any way of converting text from an AutoCad (.dwg ot .dxf) file into a 
PostgreSQL  Database??

Bob Pawley

[GENERAL] Version 8.3

2007-10-27 Thread Bob Pawley


I would like to try PostgreSQL 8.3 without uninstalling version 8.1.

Is this possible??

Bob Pawley


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] pg_restore

2007-10-28 Thread Bob Pawley


Please help.

I am attempting to restore a database into PostgreSQL version 8.2 running on 
Win XP Professional.



From the 'bin' folder, I am using the command line-

pg_restore psql -h localhost -d PDW -U postgres -f aurel.sql

I get an error -

pg_restore: cannot specify both -d and -f output.

If the error message is correct how does pg_restore know what to put where?

I used the same command to successfully install the same pg_dump file into 
PostgreSQL 8.1 running on the same computer.


Any thoughts would be much appreciated.

Bob Pawley



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] pg_restore

2007-10-28 Thread Bob Pawley


Hi Adrian

With  pg_restore psql -h localhost -d PDW -U postgres  aurel.sql the error 
message is -


pg_restore: could not open input file: No such file or directory exists.

I get this message with aurel.sql - or aurel - or the path to aurel 
(..8,2\bin) or  when aurel is not even mentioned.


This is becoming quite frustrating.

Bob
- Original Message - 
From: "Adrian Klaver" <[EMAIL PROTECTED]>

To: 
Cc: "Bob Pawley" <[EMAIL PROTECTED]>
Sent: Sunday, October 28, 2007 11:45 AM
Subject: Re: [GENERAL] pg_restore



On Sunday 28 October 2007 11:32 am, Bob Pawley wrote:

Please help.

I am attempting to restore a database into PostgreSQL version 8.2 running
on Win XP Professional.

From the 'bin' folder, I am using the command line-
pg_restore psql -h localhost -d PDW -U postgres -f aurel.sql


Try  pg_restore psql -h localhost -d PDW -U postgres  aurel.sql
No -f switch



I get an error -

pg_restore: cannot specify both -d and -f output.

If the error message is correct how does pg_restore know what to put 
where?


The -d switch tells pg_restore to the named database. The -f switch tells 
it

to restore to named file. It won't do both.



I used the same command to successfully install the same pg_dump file 
into

PostgreSQL 8.1 running on the same computer.


Maybe 8.1 ignored the error.



Any thoughts would be much appreciated.




--
Adrian Klaver
[EMAIL PROTECTED] 



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] pg_restore

2007-10-28 Thread Bob Pawley


The latest in the saga -

By using - pg_restore  -h localhost -d PDW -U postgres  aurel.sql

I get the message - pg_restore: input file does not appear to be a valid 
archive.


I get this message when I used the aurel.sql file which I previously loaded 
successfully in 8.1 and also when I use an aurel.sql file which I just 
successfully dumped a few minutes ago from the 8.1 on my other computer.


Could pg_restore in my 8.2 be corrupted??

Bob
- Original Message - 
From: "Tom Lane" <[EMAIL PROTECTED]>

To: "Bob Pawley" <[EMAIL PROTECTED]>
Cc: "Adrian Klaver" <[EMAIL PROTECTED]>; 
Sent: Sunday, October 28, 2007 2:28 PM
Subject: Re: [GENERAL] pg_restore



Bob Pawley <[EMAIL PROTECTED]> writes:

This is becoming quite frustrating.


The errant "psql" is your problem ... although pg_restore is being
quite unhelpful by not mentioning the filename that it's trying to open.

regards, tom lane 



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] pg_restore

2007-10-28 Thread Bob Pawley


This is the dump command

pg_dump -h localhost -d  Aurel -U postgres

Could you suggest a dump command that will match the restore command -

pg_restore  -h localhost -d PDW -U postgres  aurel.sql


Thanks

Bob

- Original Message - 
From: "Adrian Klaver" <[EMAIL PROTECTED]>

To: 
Cc: "Bob Pawley" <[EMAIL PROTECTED]>; "Tom Lane" <[EMAIL PROTECTED]>
Sent: Sunday, October 28, 2007 3:15 PM
Subject: Re: [GENERAL] pg_restore



On Sunday 28 October 2007 3:01 pm, Bob Pawley wrote:

The latest in the saga -

By using - pg_restore  -h localhost -d PDW -U postgres  aurel.sql

I get the message - pg_restore: input file does not appear to be a valid
archive.

I get this message when I used the aurel.sql file which I previously 
loaded

successfully in 8.1 and also when I use an aurel.sql file which I just
successfully dumped a few minutes ago from the 8.1 on my other computer.

Could pg_restore in my 8.2 be corrupted??

Bob
What does your dump command look like? My guess is your are doing a plain 
text

dump and pg_restore only works with the custom formats. If you want to use
the plain text version than you need to use psql. This maybe how you got 
to

the point of having both pg_restore and psql on the same line.
--
Adrian Klaver
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match 



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] pg_restore

2007-10-29 Thread Bob Pawley


Following the examples in the docs I've come to this.

I am attempting to restore the existing sql dump using
psql -d PDW -f aurel.sql

I am then asked for a password.

I try every password that the computer knows with no success.

Funny thing the password cursor doesn't move when inputting the password.

I keep getting authentication failure.

When I attempt to do a new pg_dump with -Fc I also get a request for 
password with identical results.


Bob



- Original Message - 
From: "Adrian Klaver" <[EMAIL PROTECTED]>

To: 
Cc: "Bob Pawley" <[EMAIL PROTECTED]>; "Tom Lane" <[EMAIL PROTECTED]>
Sent: Sunday, October 28, 2007 3:58 PM
Subject: Re: [GENERAL] pg_restore



On Sunday 28 October 2007 3:40 pm, Bob Pawley wrote:

This is the dump command

pg_dump -h localhost -d  Aurel -U postgres

Could you suggest a dump command that will match the restore command -

pg_restore  -h localhost -d PDW -U postgres  aurel.sql


Thanks

Bob

It depends on what you want to do. But to use pg_restore you will need to 
use
one of either -Fc or Ft after the pg_dump command. My concern is that you 
are

connecting to a different database name in the dump and restore commands.
This may be what you want, but then again it may not.I would suggest 
reading

the information at the URL below before proceeding further.
http://www.postgresql.org/docs/8.2/interactive/app-pgdump.html

--
Adrian Klaver
[EMAIL PROTECTED] 



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] PostgreSQL and AutoCad

2007-10-30 Thread Bob Pawley

If your holy grail is the ability of using infomation to drive drawings I 
have to ask if you have any idea what that could lead too?

- Design productivity would increase by factors of hundreds - perhaps 
thousands.

- Information would be infinitly adaptable.

- Structure that information properly and knowedge will result.

- We would begin to realize the full potential of computing power.

Is that what you were saying??

Bob

- Original Message - 
From: "Richard Broersma Jr" <[EMAIL PROTECTED]>

To: ; "Andy" <[EMAIL PROTECTED]>
Sent: Monday, October 29, 2007 9:13 PM
Subject: Re: [GENERAL] PostgreSQL and AutoCad

--- On Thu, 10/25/07, Andy <[EMAIL PROTECTED]> wrote:

>> Is there any way of converting text from an
AutoCad (.dwg ot .dxf) file into
>> a PostgreSQL  Database??
Do you want AutoCad to edit the drawings right out of the
database?  How
would you want to put them in/get them out, of the
database?

I think the more traditional problem is to extract information embedded 
(within blocks) in a drawing to produce a bill of material.  As long as 
the text is stored in a block it is a trivial task.  On the other hand, if 
the text is free floating in the drawing, finding it is a little more 
difficult but still possible using lisp or vba.

Auto cad has prebuilt tools to extract/link data from blocks to any ODBC 
compliant database.  Of course, the holy grail would be to eliminate auto 
cad altogether and then render drawings from the data stored in the 
database. :-)

Regards,
Richard Broersma Jr.

---(end of broadcast)---
TIP 6: explain analyze is your friend 

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] PostgreSQL and AutoCad

2007-10-30 Thread Bob Pawley

Thanks Ilan this looks promising.

Bob
  - Original Message - 
  From: Ilan Volow 
  To: pgsql-general@postgresql.org 
  Sent: Tuesday, October 30, 2007 3:01 PM
  Subject: Re: [GENERAL] PostgreSQL and AutoCad

  As I have a vested interest in storing AutoCad stuff in PostgreSQL, I 
searched for something like this a while ago and I ran across this.. I haven't 
really had a chance to play with it yet

  http://sourceforge.net/projects/dxf2postgis/

  I'm personally interested in the idea of versioning for a drawing. Instead of 
storing the entire drawing for each version, one could theoretically just store 
the vector additions/changes/deletions that happen from one revision to the 
next.

  -- Ilan

  On Oct 30, 2007, at 11:34 AM, Bob Pawley wrote:

If your holy grail is the ability of using infomation to drive drawings I 
have to ask if you have any idea what that could lead too?

- Design productivity would increase by factors of hundreds - perhaps 
thousands.

- Information would be infinitly adaptable.

- Structure that information properly and knowedge will result.

- We would begin to realize the full potential of computing power.

Is that what you were saying??

Bob

- Original Message - From: "Richard Broersma Jr" <[EMAIL PROTECTED]>
To: ; "Andy" <[EMAIL PROTECTED]>
Sent: Monday, October 29, 2007 9:13 PM
Subject: Re: [GENERAL] PostgreSQL and AutoCad

  --- On Thu, 10/25/07, Andy <[EMAIL PROTECTED]> wrote:
>> Is there any way of converting text from an
AutoCad (.dwg ot .dxf) file into
>> a PostgreSQL  Database??
Do you want AutoCad to edit the drawings right out of the
database?  How
would you want to put them in/get them out, of the
database?

  I think the more traditional problem is to extract information embedded 
(within blocks) in a drawing to produce a bill of material.  As long as the 
text is stored in a block it is a trivial task.  On the other hand, if the text 
is free floating in the drawing, finding it is a little more difficult but 
still possible using lisp or vba.

  Auto cad has prebuilt tools to extract/link data from blocks to any ODBC 
compliant database.  Of course, the holy grail would be to eliminate auto cad 
altogether and then render drawings from the data stored in the database. :-)
  Regards,
  Richard Broersma Jr.

  ---(end of broadcast)---
  TIP 6: explain analyze is your friend 

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

  Ilan Volow
  "Implicit code is inherently evil, and here's the reason why:"

[GENERAL] Serial IDs

2007-11-14 Thread Bob Pawley


Hi

Is there any method of clearing the serial numbering so that ID references 
can start afresh without rebuilding the database.


Of I use postgresql as part of my application I would like to use  pgdump to 
ensure that I have the latest version and starting the serial numbering at 
#1 would be a good thing.


Bob 



---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] PostgreSQL on the internet

2007-12-03 Thread Bob Pawley

Hi

I am kicking around the idea of using PostgreSQL as a web based service. Access 
to the site will be through a separate application/interface.

The user's interface will install the database on entry to the website. When 
the user exits the site, the database will be dumped to the user's computer and 
eliminated from the website. It seems to me that it is possible to make this 
dump and restore invisible, or mostly invisible,  to the user.

Can someone tell me what criteria I need to look at in order to determine how 
many clients can be using the website, each with their own database, at one 
time??

If you see any other challenges to this plan, I would appreciate it if you 
would please let me know.

Bob Pawley

Re: [GENERAL] PostgreSQL on the internet

2007-12-03 Thread Bob Pawley


At the moment the database dump is 4.1 meg.

I suspect the end result will be less than 10 meg including the user's 
information.


Is there other size information you need?

Bob




- Original Message - 
From: "Richard Huxton" <[EMAIL PROTECTED]>

To: "Bob Pawley" <[EMAIL PROTECTED]>
Cc: "PostgreSQL" 
Sent: Monday, December 03, 2007 8:18 AM
Subject: Re: [GENERAL] PostgreSQL on the internet



Bob Pawley wrote:

Hi

I am kicking around the idea of using PostgreSQL as a web based
service. Access to the site will be through a separate
application/interface.


Why web-based if you're installing an application to the user's PC.


Can someone tell me what criteria I need to look at in order to
determine how many clients can be using the website, each with their
own database, at one time??


How big is each database, and what will the users be doing with them?

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings 



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] PostgreSQL on the internet

2007-12-03 Thread Bob Pawley

Basically, the database will be used to build up an engineering document 
called a P&ID which traditionally  comes in the form of a drawing. From the 
database I'll create a drwawing which the user can, if he wants, convert 
into a DXF file for use in AutoCad or Bentley drawing systems.


The database will also be used to transfer information to other engineering 
databases yet to be completed.


Bob



- Original Message - 
From: "Scott Marlowe" <[EMAIL PROTECTED]>

To: "Bob Pawley" <[EMAIL PROTECTED]>
Cc: "PostgreSQL" 
Sent: Monday, December 03, 2007 8:35 AM
Subject: Re: [GENERAL] PostgreSQL on the internet



On Dec 3, 2007 9:33 AM, Bob Pawley <[EMAIL PROTECTED]> wrote:


Hi

I am kicking around the idea of using PostgreSQL as a web based service.
Access to the site will be through a separate application/interface.

The user's interface will install the database on entry to the website. 
When
the user exits the site, the database will be dumped to the user's 
computer
and eliminated from the website. It seems to me that it is possible to 
make

this dump and restore invisible, or mostly invisible,  to the user.


You mean, I suppose, that you'll create a db on your server for the
user, and load schema / data into that?  Then dump it out to the user
when they're ready to go, and drop the db on your end?

Seems pretty easily doable to me.


Can someone tell me what criteria I need to look at in order to determine
how many clients can be using the website, each with their own database, 
at

one time??


How much memory your db server has, how big the db will be, what kind
of load it will have, how the db is tuned, etc...  I'd try it out
after building a simple test case db and see how the system behaves
with say 1, 2, 4, 10, 20 etc users.  Get a feel for it.

I think a bit more information from you on exactly what you're
planning to do would help to determine an upper limit on how many
users you can handle.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/ 



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] pg_dump

2007-12-07 Thread Bob Pawley

Thanks Erik. In between those attempts I did try what you suggest.

It failed, apparently due to not making a connection with the server.

Bob
- Original Message - 
From: "Erik Jones" <[EMAIL PROTECTED]>

To: "Bob Pawley" <[EMAIL PROTECTED]>
Cc: "PostgreSQL" 
Sent: Friday, December 07, 2007 9:58 AM
Subject: Re: [GENERAL] pg_dump

On Dec 7, 2007, at 11:38 AM, Bob Pawley wrote:

Hi

I am having a little more success with the pg_dump command.  However, I 
still seem to have something wrong.

I use the following command after navigating to the bin file -

pg_dump -h localhost -d Aurel -U postgres

After six attempts -

Each attempt processed the database and the command prompt  announced a 
successful completion.

after 3 attempts - no dump file was found
after 2 attempts - a dump file of 0kb was found.
after the sixth attempt a good dump file of 4 meg made it to the  bin 
folder. I was able to install this file successfully.

Is it usual to require multiple attempts for each successful dump???

If not, does anyone have thoughts on where  the problem could be??

If that's all you're doing then I'm surprised you ever saw a file at
all.  pg_dump writes to standard output unless you give it a file
name to write to with the -f flag.  So, from your bin directory (I
normally just put it in my environment PATH variable).

./pg_dump -h localhost -d Aurel -U postgres -f dumpfile.sql

or

.pg_dump -h localhost -d Aurel -U postgres > dumpfile.sql

Erik Jones

Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] pg_dump

2007-12-07 Thread Bob Pawley


Hi

I am having a little more success with the pg_dump command. However, I still 
seem to have something wrong.


I use the following command after navigating to the bin file -

pg_dump -h localhost -d Aurel -U postgres

After six attempts -

Each attempt processed the database and the command prompt announced a 
successful completion.


after 3 attempts - no dump file was found
after 2 attempts - a dump file of 0kb was found.
after the sixth attempt a good dump file of 4 meg made it to the bin folder. 
I was able to install this file successfully.


Is it usual to require multiple attempts for each successful dump???

If not, does anyone have thoughts on where  the problem could be??

Bob Pawley 



---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Graphics

2008-01-21 Thread Bob Pawley

I use graphics in the application I am writing with PostgreSQL as the 
database.


I am storing the graphics in a file separate from Postgres and accessing 
them using lo_import.


Is there any method of incorporating the graphics into PostgreSQL so that 
they become part of the database on pg_dump


Bob 



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[GENERAL] Count

2008-01-23 Thread Bob Pawley

I have a table with four columns that will either be null or hold the value 
'true'.


I want to obtain the count of these columns, within a particular row, that 
have 'true' as a value (0 to 4).


I have attempted the Select count method but it seems that I need something 
more.


If anyone has any thoughts it would be much appreciated.

Bob 



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] How to determine failed connection attempt due to invalid authorization (libpq)?

2010-10-15 Thread Bob Pawley

Hi Dimitriy

Did you ever get an answer to your query regarding connection security.

I`m not only interested in this aspect, I am also interested in how to detect 
any unauthorized connection attempt whether through the front door or the back 
door.

Bob


From: Dmitriy Igrishin 
Sent: Wednesday, October 13, 2010 11:58 AM
To: pgsql-general@postgresql.org 
Subject: [GENERAL] How to determine failed connection attempt due to invalid 
authorization (libpq)?


Hey all,

We using libpq. There is only CONNECTION_BAD status which
is signaled about failed connection. Nevertheless, is there way
to check  validity of username / password ?

-- 
// Dmitriy.

[GENERAL] Root user commands

2010-12-22 Thread Bob Pawley

Hi

I am attempting to see if my Postgresql installation is running.

I’ve found this -
[root user only] ./database_service.pl status

I don’t understand what is meant by root user.

I also don’t know how “./database_service.pl status” is used.

Bob

1 2 3 4 5 6 >

1 - 100 of 502 matches

Mail list logo