Обсуждение: ISP/DNS change
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.
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
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 :
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
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
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
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
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....
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
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
>
>
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 > >