[AOLSERVER] Aolserver - Postgresql - not recognizing columns added to tables

[Thorpe Mayes]

Hi,

I have moved postgresql databases from one server to another. This was done by dumping the database, moving the resulting file to the new server, and then restoring the dumped file on the new server.

When I directly select rows via postgresdql (on the new server) from a table that had a column added after the table was created and before the table was moved to the new server there are not any problems.

However, when I try to do the same select from within a tcl script an error is thrown - the column does not exist. When I run the sql statement without the offending column, there is not a problem. 

So, it appears to me that the problem is with columns that have been added to tables via alter table…

Can anyone shed some light on this problem?

Thank you,

Thorpe

Thorpe Mayes

eCognizant LLC

2313 Lockhill-Selma Road, Ste 164

San Antonio, TX 78230

Phone: (405) 445-7877

Cell: (405) 514-9753

[Ayan George]

On 08/09/2015 03:03 PM, Thorpe Mayes wrote:
> Hi,
>
> I have moved postgresql databases from one server to another. This
> was done by dumping the database, moving the resulting file to the
> new server, and then restoring the dumped file on the new server.
>
> When I directly select rows via postgresdql (on the new server) from
> a table that had a column added after the table was created and
> before the table was moved to the new server there are not any
> problems.
>
> However, when I try to do the same select from within a tcl script
> an error is thrown - the column does not exist. When I run the sql
> statement without the offending column, there is not a problem.
>
> So, it appears to me that the problem is with columns that have been
> added to tables via alter table…
>
> Can anyone shed some light on this problem?
>

What version of PostgreSQL are you running and which version of libpq is
your nspostgres linked against?

When you moved servers, did you also change the version of postgres
you're using?

-ayan

------------------------------------------------------------------------------
_______________________________________________
aolserver-talk mailing list
aolserv...@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/aolserver-talk

[Ayan George]

Also, can you see the new columns by searching the information schema in
both psql and aolserver?

[Peter Sadlon]

Just to better understand your problem, this was the order of events?

old server: create table

old server: alter table add column

old server: select any/all columns work

copy to new server

new server: select from original columns work with postgresdql 

new server: select from an added column works with postgresdql 

new server: select from original columns work with tcl 

new server: select from an added column FAILS with tcl 

My first guess would be to make sure your config script is connecting to the correct database/server now that you have multiple db servers.  Same thing for postgresdql.  

Are you connecting with the same username via postgresdql and tcl?

Next check your pg_dump file, do a grep for the new column name, make sure that it is included in the dump.

---

From: tma...@ecognizant.com
Date: Sun, 9 Aug 2015 14:03:28 -0500
To: aolserv...@lists.sourceforge.net
Subject: [AOLSERVER] Aolserver - Postgresql - not recognizing columns added to tables

[Sep]

I don't quite remember my postgresql, but I'm presuming that maybe postgresql has something like a data dictionary like in Oracle that needs syncing perhaps?

Regards.

--

"A scrum a day keeps the pigs at bay"

[Tony Bennett (Brown Paper Tickets)]

The same select should work in tcl for the added column.  Try updating a record in postgresql and selecting the same record from tcl. If there's a mismatch then they're not connecting to the same place.

-Tony

[Tony Bennett (Brown Paper Tickets)]

Also I'm curious to know if there's anything special about the new columns.  Are they encrypted or a strange datatype?  If it's a newish type of field then the tcl might need updating to use it.

[Thorpe Mayes]

Hi,

Here is where postgres is located on the server:

ps auxw |  grep postgres | grep -- -D

postgres  1740  0.0  0.0 160284  7616 ?        S    02:49   0:00 /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data

Here is how I installed nspostgres-4.1:

make install AOLSERVER=/usr/local/aolserver  POSTGRES=/usr/local/pgsql

Thorpe

_______________________________________________ aolserver-talk mailing list aolserv...@lists.sourceforge.nethttps://lists.sourceforge.net/lists/listinfo/aolserver-talk

------------------------------------------------------------------------------

_______________________________________________
aolserver-talk mailing list
aolserv...@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/aolserver-talk

------------------------------------------------------------------------------
_______________________________________________
aolserver-talk mailing list
aolserv...@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/aolserver-talk