Thread: ISP/DNS change

ISP/DNS change

From
Jun Tanamal
Date:
We had a working postgresql before we changed to a different ISP and
changed the DNS settings.
I recently noticed that postgresql failed.I am using
apache+php4+postgresql. I haven't done any changes so far with postgresql.
I can connect to the database directly but not through apache using the
php files.

What diagnostic procedures will I follow?
thanks in advance.

Jun

p.s. it was my predecessor who set it up.



Re: ISP/DNS change

From
"Michael Paesold"
Date:
Jun Tanamal <jtanamal@ssd.org> wrote:

> We had a working postgresql before we changed to a different ISP and
> changed the DNS settings.
> I recently noticed that postgresql failed.I am using
> apache+php4+postgresql. I haven't done any changes so far with postgresql.
> I can connect to the database directly but not through apache using the
> php files.
>
> What diagnostic procedures will I follow?
> thanks in advance.
>
> Jun

Some questions:
The postgresql server is located at your new ISP?
Have you changed the connect strings in your php scripts?
 (hostname of postgres server, port, username, password?)
What exactly do you mean by "connect directly"?

Regards,
Michael Paesold


Re: ISP/DNS change

From
Garrett Bladow
Date:
If your IP changed you may need to edit your pg_hba.conf.


---- This is what you wrote me ----

:We had a working postgresql before we changed to a different ISP and
:changed the DNS settings.
:I recently noticed that postgresql failed.I am using
:apache+php4+postgresql. I haven't done any changes so far with postgresql.
:I can connect to the database directly but not through apache using the
:php files.
:
:What diagnostic procedures will I follow?
:thanks in advance.
:
:Jun
:
:p.s. it was my predecessor who set it up.
:
:
:
:---------------------------(end of broadcast)---------------------------
:TIP 3: if posting/reading through Usenet, please send an appropriate
:subscribe-nomail command to majordomo@postgresql.org so that your
:message can get through to the mailing list cleanly
:


update question

From
DAVID KUCHARSKI
Date:
OK I'm new at this, so please bear with me.
I have a postgres table that gets imported from a text
output of a database - uns_output.
There is another table that was created by hand with
our entire inventory - inventory.  I would like to
update a price column in the inventory table from the
information in the price column of the output table.
I'm quite sure it can be done, but I'm not really a
trained dba and i this is beyond the scope of anything
I've done in the past.  Both tables are indexed on a
common field called pik_num.  Our inventory is only a
small portion of the uns_output file so just importing
ALL of that info would be WAY OVERKILL.
I'm assuming that this would be a multiple step
process, first to find which items in inventory are in
uns_output.  then to update those from the appropriate
line, but I need help with the SQL.  If anyone has the
time or inclination and could help me along or at
least point me to a place where I can find the info,
I'd be forever grateful.  I'm not even sure of any
technical terms where I should begin my search.

Thanks a BUNCH
Dave


Re: update question

From
Josh Berkus
Date:
David,

> OK I'm new at this, so please bear with me.
> I have a postgres table that gets imported from a text
> output of a database - uns_output.
> There is another table that was created by hand with
> our entire inventory - inventory.  I would like to
> update a price column in the inventory table from the
> information in the price column of the output table.
> I'm quite sure it can be done, but I'm not really a
> trained dba and i this is beyond the scope of anything
> I've done in the past.  Both tables are indexed on a
> common field called pik_num.  Our inventory is only a
> small portion of the uns_output file so just importing
> ALL of that info would be WAY OVERKILL.

Questions:
1) Is there any way you can easily identify the relevant rows in the legacy
database and export only those?

2) If the answer to 1: is "no", then is the legacy database something you
could access directly using Perl::DBI, PHP or Python, and dynamically import
only the matching rows?


--
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Re: update question

From
Josh Berkus
Date:
David,

> > > > 1) Is there any way you can easily identify the relevant rows in the
> > legacy
> > > > database and export only those?
>
> I guess I'm unclear as to which table you are
> referring to as the LEGACY table.  either way i
> imagine that by looking at a table  or VIEW of the pik
> numbers common to both tables is what i need, so yes I
> can get that. in fact i have now. but how can i use
> that table to update the price fields in inventory

No, I'm talking about the database system that is exporting to text.  Can you
tell it to export only the rows you need?


--
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Re: update question

From
DAVID KUCHARSKI
Date:
No , that one I don't have control over.  but with the
view of common numbers, I should be able to export
only the items and columns that i need to update in
the inventory file.


Josh Berkus wrote:
>
> David,
>
> > > > > 1) Is there any way you can easily identify the relevant rows in the
> > > legacy
> > > > > database and export only those?
> >
> > I guess I'm unclear as to which table you are
> > referring to as the LEGACY table.  either way i
> > imagine that by looking at a table  or VIEW of the pik
> > numbers common to both tables is what i need, so yes I
> > can get that. in fact i have now. but how can i use
> > that table to update the price fields in inventory
>
> No, I'm talking about the database system that is exporting to text.  Can you
> tell it to export only the rows you need?
>
> --
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco


Creating exponential sequences

From
Rod Kreisler
Date:
Is there any way to create an exponential sequence rather than incremental?

What I would like is a sequence that would start at 1 and grow exponentially
by 2.  (i.e. 1,2,4,8,16,32....)

The advantages are, IMHO, obvious:

With a sequence "my_seq" declared as above create a table as follows:

create table example
(
    "ID" int4 DEFAULT nextval('"my_seq"'::text) NOT NULL,
    "description" varchar(32),
     CONSTRAINT "example_pkey" PRIMARY KEY ("ID")
);
CREATE  UNIQUE INDEX "example_description" ON "example" ("description");

When referenced by another table with a 1:many relationship, instead of
using a third table, the values can be stored in a single field using a sum
of the "ID"s and reference can be queried using a logical AND.  Assuming the
referring table "example2" contains a field "example" which references the
above table with the pk on "example2" being "e2id":

select "description" from "example", "example2" where ("ID" & "example")!=0
and "e2id"=555;

Am I nuts? This seems so obvious but I've never seen it applied anywhere.
Of course, I'm by no means a db guru.

Of course, if I can't do it with a sequence, I could write a function....



Re: Creating exponential sequences

From
Tom Lane
Date:
Rod Kreisler <rod@23net.net> writes:
> Is there any way to create an exponential sequence rather than incremental?
> What I would like is a sequence that would start at 1 and grow exponentially
> by 2.  (i.e. 1,2,4,8,16,32....)

A sequence that will bomb out after 32 or 64 increments seems of limited
use ...

            regards, tom lane

Re: Creating exponential sequences

From
Rod Kreisler
Date:
OK, figured out a workaround:

create sequence "my_seq" start 0 minvalue 0 increment 1 maxvalue 63

CREATE TABLE "example" (
   "ID" int4 DEFAULT (2 ^ (nextval('"my_seq"'))) NOT NULL,
   "description" varchar(32),
   CONSTRAINT "example_pkey" PRIMARY KEY ("ID")
);
CREATE  UNIQUE INDEX "example_description" ON "example" ("description");

Obviously this will only work for small sets (i.e. <=64), but that's exactly
what I'm looking to replace.

Now just got to figure out how to write the constraints...

> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Rod Kreisler
> Sent: Monday, October 07, 2002 10:52 PM
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] Creating exponential sequences
>
>
> Is there any way to create an exponential sequence rather than
> incremental?
>
> What I would like is a sequence that would start at 1 and grow
> exponentially
> by 2.  (i.e. 1,2,4,8,16,32....)
>
> The advantages are, IMHO, obvious:
>
> With a sequence "my_seq" declared as above create a table as follows:
>
> create table example
> (
>     "ID" int4 DEFAULT nextval('"my_seq"'::text) NOT NULL,
>     "description" varchar(32),
>      CONSTRAINT "example_pkey" PRIMARY KEY ("ID")
> );
> CREATE  UNIQUE INDEX "example_description" ON "example" ("description");
>
> When referenced by another table with a 1:many relationship, instead of
> using a third table, the values can be stored in a single field
> using a sum
> of the "ID"s and reference can be queried using a logical AND.
> Assuming the
> referring table "example2" contains a field "example" which references the
> above table with the pk on "example2" being "e2id":
>
> select "description" from "example", "example2" where ("ID" &
> "example")!=0
> and "e2id"=555;
>
> Am I nuts? This seems so obvious but I've never seen it applied anywhere.
> Of course, I'm by no means a db guru.
>
> Of course, if I can't do it with a sequence, I could write a function....
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>


Re: Creating exponential sequences

From
Rod Kreisler
Date:
My last example should have used an int8.

Not in some instances.  For example, I'm working on a structure for a real
estate db.  Each property has about 15 different attributes that are best
described as sets, e.g. rooms (kitchen, living room, utility, family room,
etc)  Each set is limited and 64 would more than suffice for all of them.
As it is a 1:M join table is required to link the properties to each
attribute.  Now the number of elements in each set varies, rooms would be
the largest with an average of around 11, some will only be two or three.
We are projecting 500,000 properties within the first 6 months.  That would
equate to a 1:M join table containing 5.5 million entries.  We will almost
exclusively be querying for one property at a time, so grabbing a single
value from the main property table (which we will have to query anyway)
would be preferable.

> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: Monday, October 07, 2002 11:13 PM
> To: Rod Kreisler
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Creating exponential sequences
>
>
> Rod Kreisler <rod@23net.net> writes:
> > Is there any way to create an exponential sequence rather than
> incremental?
> > What I would like is a sequence that would start at 1 and grow
> exponentially
> > by 2.  (i.e. 1,2,4,8,16,32....)
>
> A sequence that will bomb out after 32 or 64 increments seems of limited
> use ...
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>