PgCatalog and Routines?

[Garret Wilson]

My colleague and I are both working from the same POM. We think we are generating our PostgreSQL databases from the same SQL. But our jOOQ schema output (defined in the POM using org.jooq.util.DefaultGenerator) is slightly different.

When I generate the schema, I only get the chasdb and chasdb_user subdirectories. When he generates the schema, two additional files are produced in the root of the output subtree: PgCatalog.java and Routines.java. I have no idea where those extra files are coming from.

I'll note that I created my PostgreSQL cluster (data directory) using something like this:

"C:\Program Files\PostgreSQL\9.3\bin\initdb.exe" -A md5 -U postgres -W -D "D:\example\postgres\data"

If you could tell me what those two files represent and where they might be coming from, maybe I can figure out what makes my configuration different from his.

Thanks,

Garret

[Lukas Eder]

Hi Garret,

Thank you for your report. That is indeed curious. Some things that might be different between your setups:

- You might be using a different user (with different grants) when generating code?

- You might have deleted the pg_catalog schema from your database instance, but your colleague didn't?

In any case, you should probably specify all the <inputSchema/> elements individually and exclude the pg_catalog. This is documented here:

http://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced

<!-- A configuration element to configure several input and/or output
       schemata for jooq-meta, in case you're using jooq-meta in a multi-
       schema environment.
       This cannot be combined with the above inputSchema / outputSchema -->
  <schemata>
    <schema>
      <inputSchema>...</inputSchema>
      <outputSchema>...</outputSchema>
    </schema>
    [ <schema>...</schema> ... ]
  </schemata>

What's curious, though, is the fact that the two additional files are located at the root of the output subtree. You mean that the layout is:

/output/chasdb

/output/chadb_user

/output/PgCatalog.java

/output/Routines.java

?

Best Regards,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Garret Wilson]

On 9/30/2015 12:10 PM, Lukas Eder wrote:
> ...

> - You might have deleted the pg_catalog schema from your database
> instance, but your colleague didn't?

But what does this represent in the first place? What is the pg_catalog
schema?

>
> In any case, you should probably specify all the <inputSchema/>
> elements individually and exclude the pg_catalog.

But that's why we do:

<schemata>
<schema>
<inputSchema>foodb</inputSchema>
</schema>
<schema>
<inputSchema>foodb_user</inputSchema>
</schema>

</schemata>


> What's curious, though, is the fact that the two additional files are
> located at the root of the output subtree. You mean that the layout is:
>

> /output/foodb
> /output/foodb_user
> /output/PgCatalog.java
> /output/Routines.java

Exactly.

(Of course I meant foodb and foodb_user. ;) )

Garret

[Lukas Eder]

2015-09-30 17:16 GMT+02:00 Garret Wilson <gar...@globalmentor.com>:

On 9/30/2015 12:10 PM, Lukas Eder wrote:

...
- You might have deleted the pg_catalog schema from your database instance, but your colleague didn't?

But what does this represent in the first place? What is the pg_catalog schema?

It's a PostgreSQL system schema. Like the "information_schema", too. You'll find it here:

SELECT *

FROM information_schema.schemata

What's curious, though, is the fact that the two additional files are located at the root of the output subtree. You mean that the layout is:

/output/foodb
/output/foodb_user
/output/PgCatalog.java
/output/Routines.java

Exactly.

Interesting. Can you show the full generator configuration?

(Of course I meant foodb and foodb_user. ;) )

Of course :)

[Garret Wilson]

On 9/30/2015 12:27 PM, Lukas Eder wrote:
> ...

> Interesting. Can you show the full generator configuration?

<generator>
<name>org.jooq.util.DefaultGenerator</name>
<database>
<name>org.jooq.util.postgres.PostgresDatabase</name>
<includes>.*</includes>
<excludes></excludes>

<schemata>
<schema>
<inputSchema>foodb</inputSchema>
</schema>
<schema>
<inputSchema>foodb_user</inputSchema>
</schema>
</schemata>

</database>
<strategy>
<matchers>
<fields>
<field>
<expression>^(.+):(.+)$</expression>
<fieldIdentifier>
<transform>UPPER</transform>
<expression>$1_$2</expression>
</fieldIdentifier>
<fieldSetter>
<transform>AS_IS</transform>
<expression>set$1_$2</expression>
</fieldSetter>
<fieldGetter>
<transform>AS_IS</transform>
<expression>get$1_$2</expression>
</fieldGetter>
</field>
</fields>
</matchers>
</strategy>
<target>
<packageName>com.example.foo.db.schema</packageName>
<directory>${project.build.directory}/generated-sources</directory>
</target>
</generator>

Garret

[Lukas Eder]

Hmm, perhaps a matcher strategy bug, then, combined perhaps with subtly different database configurations between you and your coworker. Does the problem persist when you remove the <strategy/> element?

Garret

[Garret Wilson]

On 9/30/2015 12:27 PM, Lukas Eder wrote:

...

What's curious, though, is the fact that the two additional files are located at the root of the output subtree. You mean that the layout is:

/output/foodb
/output/foodb_user
/output/PgCatalog.java
/output/Routines.java

Exactly.

Interesting.

Does "interesting" mean "that's where I would expect them to be" or "I would expect them to appear and some other such and such location"?

Garret

[Lukas Eder]

2015-09-30 17:44 GMT+02:00 Garret Wilson <gar...@globalmentor.com>:

On 9/30/2015 12:27 PM, Lukas Eder wrote:

...

What's curious, though, is the fact that the two additional files are located at the root of the output subtree. You mean that the layout is:

/output/foodb
/output/foodb_user
/output/PgCatalog.java
/output/Routines.java

Exactly.

Interesting.

Does "interesting" mean "that's where I would expect them to be"

Yes, if you had only generated the PgCatalog, and no other schemas.

 

or "I would expect them to appear and some other such and such location"?

With multiple schemas, the files should appear in a "pg_catalog" sub package.

But since you didn't include this schema, the files shouldn't appear at all! 

[Lukas Eder]

Hi Garret,

I've played around with the code generator configuration, and I'm afraid, I couldn't reproduce this issue as you've reported it. The only time there were any PgCatalog.java and Routines.java files was when I explicitly generated the "pg_catalog" schema, and then, the files were located in the correct subpackage called pg_catalog.

Do you perhaps have some additional information / insight about how to reproduce this issue?

Best Regards,

Lukas

[Garret Wilson]

Hi, Lukas! I'm going through some old emails---sorry I never responded to this one. Anyway---I no longer have the client, and it was my coworker's problem anyway, so... for the meantime you can close whatever issues you have associated (if any) with this.

Cheers,

Garret

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/44yXii9pDnU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Hi Garret,

Thank you very much for the update.

No worries, it happens to the best. And as you've noticed yourself, after waiting long enough, some things just disappear by themselves. :) I do hope you'll be using jOOQ again for another client, though!

Since I couldn't reproduce it at the time, I haven't created any issues yet.

Cheers,
Lukas

--

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Ed Erwin]

I am the other person Garret was referring to.  This was never a bug in JOOQ but likely a miscommunication between us or confusion on behalf of one of us.  

At some point either me or another developer here intentionally generated JOOQ code for the "pg_catalog" schema methods md51 and md52 so that we could reference them from Java.  We later decided we didn't need to reference those methods anymore.

I came across this post because I was just today re-generating the source code and trying to figure out why these methods weren't also regenerated.

For future reference for myself, if I ever do need to generate source for these pg_catalog methods, this is how to configure it:

              <generator>

                <name>org.jooq.util.DefaultGenerator</name>

                <database>

                  <name>org.jooq.util.postgres.PostgresDatabase</name>

                  <includes>md5*</includes>

                  <excludes></excludes>

                  <schemata>

                    <schema>

                      <inputSchema>pg_catalog</inputSchema>

                    </schema>

                          </schemata>

                </database>

On Monday, April 11, 2016 at 1:46:36 AM UTC-7, Lukas Eder wrote:

Hi Garret,

[Lukas Eder]

Hi Ed,

Thank you very much for the feedback! Excellent idea to add some additional info for future reference, in case you google this again :)

Cheers,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.